I would expect your gonna need an XML schema that describes tha data and datatypes.
-----Original Message----- From: Matt Horn [mailto:[EMAIL PROTECTED]] Sent: 12 September 2002 16:03 To: [EMAIL PROTECTED] Subject: [ cf-dev ] XML + SQL problems 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] -- ** 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]
