Tried same problem
At 10:56 13/09/02 +0100, you wrote: >Only an idea (not something I've tried) but does it make any difference if >you use an empty element eg <Age/> rather than an element containing a zero >length string? > >Adrian >_____________________________________________ >adrian marshall >lead hat limited >28-29 great sutton street . london . ec1v 0ds >http://www.leadhat.co.uk/ >e: [EMAIL PROTECTED] >p: 020 7566 9450 >f: 020 7566 9458 > > >----- Original Message ----- >From: "Matt Horn" <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]> >Sent: Thursday, September 12, 2002 5:09 PM >Subject: Re: [ cf-dev ] XML + SQL problems > > > > that defeats the purpose of using XML here > > as SQL2000 does deal with raw XML > > OPENXML is supposed to open the XML and parse the data. > > > > Russ we are using a schema declaration > > which identifies the datatype of each XML element > > > > any other ideas? > > > > Matt > > > > > > > > > > At 17:05 12/09/02 +0100, you wrote: > > >You could use XSLT to transform your xml into SQL insert statements. Then > > >you could have conditional logic in there to insert NULLs for empty >elements. > > > > > > >>> Matt Horn <[EMAIL PROTECTED]> 09/12 4:03 pm >>> > > >I have some data in an xml format which I want to insert into a SQL 2000 > > >db table. > > > > > >e.g. The db table has the following fields: > > >PersonID int(4) > > >FirstName varchar(50) > > >LastName varchar(50) > > >Age int(4) NULL > > >Salary decimal NULL > > >Email varchar(50) NULL > > > > > >The xml is in the following form: > > ><PERSON> > > > <PersonID>1</PersonID> > > > <FirstName>John</FirstName> > > > <LastName>Smith</LastName> > > > <Age></Age> > > > <Salary></Salary> > > > <Email></Email> > > ></PERSON> > > > > > >I've written a stored procedure which uses SQL 2000 OPENXML to parse the > > >XML and insert the data into the table. Which it does successfully when > > >all the XML elements have data in them. > > > > > >However, since the last 3 columns in the DB are Nullable, I thought that > > >if the last 3 elements in the XML were empty, SQL 2000 would insert Null > > >into the db. What happens though is this: > > >The "Age" field, int(4) gets an automatic value of 0 entered. > > >"Salary" field, decimal, throws an error of "error converting nvarchar to > > >numeric". > > >Email field, varchar, populated with an empty string. > > > > > >Does anyone know how to get SQL to insert NULLs in these cases? > > > > > >Sean > > > > > >Matt Horn > > >Senior Web Applications Developer > > >MediaTel Group > > >84-86 Regent Street > > >London > > >W1B 5AJ > > >Tel: +44(0)20 7439 7575 > > >Fax: +44(0)20 7734 0940 > > > > > >MediaTel Group - Maximising efficiency across the entire media process. > > > > > >www.mediatelgroup.co.uk > > > > > >This message is for the named recipient's use only and may contain > > >confidential or privileged information. If you receive this message in > > >error, please immediately delete it and all copies of it from your >system, > > >destroy any hard copies of it and notify the sender. You must not, >directly > > >or indirectly, use, disclose, distribute, print, or copy any part of this > > >message if you are not the intended recipient. Email communications are >not > > >secure and therefore the MediaTel Group does not accept liability for any > > >errors or omissions in the contents of this message which arise as a >result > > >of email transmission. MediaTel reserves the right to monitor all email > > >communications through its networks. Any views expressed by an individual > > >in this email do not necessarily reflect the views of the MediaTel Group. > > > > > > > > >-- > > >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > > > >To unsubscribe, e-mail: [EMAIL PROTECTED] > > >For additional commands, e-mail: [EMAIL PROTECTED] > > >For human help, e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > >*************************************************************************** > > >This message has been swept for viruses and other > > >unsuitable content by the Ofsted Mail Sweeper in > > >accordance with OFIN 59, however care should be > > >taken when opening any attachments from an untrusted > > >source. > > > >*************************************************************************** > > > > > > > > > > > > > > >********************************************************************** > > >We cannot accept any liability for any loss or damage > > >sustained as a result of software viruses. It is your > > >responsibility to carry out such virus checking as is necessary before > > >opening any attachment to > > >this message. > > > > > >The information in this email and any files transmitted with > > >it may be of a confidential nature and is intended solely for > > >the addressees. If you are not the intended addressee, any > > >disclosure, copying or distribution by you is prohibited and > > >may be unlawful. > > > > > >www.ofsted.gov.uk > > >********************************************************************** > > > > > > > > >-- > > >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > > > >To unsubscribe, e-mail: [EMAIL PROTECTED] > > >For additional commands, e-mail: [EMAIL PROTECTED] > > >For human help, e-mail: [EMAIL PROTECTED] > > > > > > -- > > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > For human help, e-mail: [EMAIL PROTECTED] > > > > > > >-- >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
