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]
