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]
