the solution is not to pass the empty XML elements
<element>
<child>1</child>
<child>2</child>
<child>4</child>
<child></child>
</element>
basically you DON't put in
<child></child>
and it works fine
*grumble*
At 11:23 13/09/02 +0100, you wrote:
>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]
--
** 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]
