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]
