Data XML file to SQL Server Table...

Hi,

I have the following XML document, which I want to put into SQL Server 2000 Table. How can I do that? The table name is "Orders". I am using C#.

<Order>

<Date>2007/09/02</Date>

<OrderID>200</OrderID>

<PartNumber id = “ABC10”>

<Delivery_Date>

</Delivery Date>

<Delivery_Quanity>

</Delivery_Quanity>

<Price>

</Price>

</PartNumber>

<PartNumber id = “ABC98”>

<Delivery_Date>

</Delivery Date>

<Delivery_Quanity>

</Delivery_Quanity>

<Price>

</Price>

</PartNumber>

<PartNumber id = “ABC11”>

<Delivery_Date>

</Delivery Date>

<Delivery_Quanity>

</Delivery_Quanity>

<Price>

</Price>

</PartNumber>

</Order>

Any help would be highly appreciated...

Regards,

Hi again,

Just to explain my question again,

I want the table to look like the following,

Orders:

Auto: Date: OrderID: Partnumber: Delivery_Date: Delivery_Quantity: Price:

0001 2007/09/02 200 ABC10 0 1 0

0002 2007/09/02 200 ABC10 0 2 0

0003 2007/09/02 200 ABC98 0 56 0

0004 2007/09/02 200 ABC11 0 34 0

Orders.XML

<Order>

<Date>2007/09/02</Date>

<OrderID>200</OrderID>

<PartNumber id = “ABC10”>

<Delivery_Date> <!--Can have multiple Delivery_Date, Delivery_Quantity and Price-->

</Delivery Date>

<Delivery_Quanity>

1

</Delivery_Quanity>

<Price>

</Price>

<Delivery_Date> <!--Can have multiple Delivery_Date, Delivery_Quantity and Price-->

</Delivery Date>

<Delivery_Quanity>

2

</Delivery_Quanity>

<Price>

</Price>

</PartNumber>

<PartNumber id = “ABC98”>

<Delivery_Date>

</Delivery Date>

<Delivery_Quanity>

56

</Delivery_Quanity>

<Price>

</Price>

</PartNumber>

<PartNumber id = “ABC11”>

<Delivery_Date>

</Delivery Date>

<Delivery_Quanity>

34

</Delivery_Quanity>

<Price>

</Price>

</PartNumber>

</Order>

Regards,

Data Type for multi line text?

Hi,
I'm using bulkload to import data from an xml file. The data has several
lines of text before the closing tag. After I bulkload the data all the text
is ran together.
I'm using data type of "Text" on my SQL2005 server, should I use something
else that will keep the formatting? The text can actuall be over several
thousand characters.
Thanks
Charles W
XML format:
<data> Line one with data
Line two with data, a line may be skipped
Fourth line with data.
</data>
SQL format:
Line one with dataLine two with data, a line may be skippedFourth line with
data.
Hello Charles,

> Hi,
> I'm using bulkload to import data from an xml file. The data has
> several
> lines of text before the closing tag. After I bulkload the data all
> the text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use
> something
> else that will keep the formatting? The text can actuall be over
> several
> thousand characters.
> Thanks
> Charles W
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
> with
> data.
is nvarchar(max) an option?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:eWjOBAsTGHA.4976@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm using bulkload to import data from an xml file. The data has several
> lines of text before the closing tag. After I bulkload the data all the
text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use something
> else that will keep the formatting? The text can actuall be over several
> thousand characters.
>
> Thanks
> Charles W
>
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
with
> data.
>
How are you confirmin that the line feeds are actually being removed?
Note that XML will often replace any CRLF sequence with a simple LF.
Could it be that the LFs are there but what you are using to retreive and
display the value requires CRLFs?
Anthony.
|||I thought changing the field to nvarchar(4000) worked, but I ran into a size
problem when processing my files. It seems that some of the data is over the
4000 max.
Any other ideas?
Thanks
CW
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741dc7f8c81cab6345f890@.news.microsoft.co m...
> Hello Charles,
>
> is nvarchar(max) an option?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:OxLl6xbUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I thought changing the field to nvarchar(4000) worked, but I ran into a
size
> problem when processing my files. It seems that some of the data is over
the
> 4000 max.
> Any other ideas?
>
> Thanks
> CW
>
NText is the field type you need. Still doesn't eliminate the Line feed
issue which as already pointed out is not a function of the SQL data type
you are choosing but is just how XML works.
See:-
http://www.w3.org/TR/REC-xml/#sec-line-ends
Anthony

Data Type for multi line text?

Hi,
I'm using bulkload to import data from an xml file. The data has several
lines of text before the closing tag. After I bulkload the data all the text
is ran together.
I'm using data type of "Text" on my SQL2005 server, should I use something
else that will keep the formatting? The text can actuall be over several
thousand characters.
Thanks
Charles W
XML format:
<data> Line one with data
Line two with data, a line may be skipped
Fourth line with data.
</data>
SQL format:
Line one with dataLine two with data, a line may be skippedFourth line with
data.Hello Charles,

> Hi,
> I'm using bulkload to import data from an xml file. The data has
> several
> lines of text before the closing tag. After I bulkload the data all
> the text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use
> something
> else that will keep the formatting? The text can actuall be over
> several
> thousand characters.
> Thanks
> Charles W
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
> with
> data.
is nvarchar(max) an option?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:eWjOBAsTGHA.4976@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm using bulkload to import data from an xml file. The data has several
> lines of text before the closing tag. After I bulkload the data all the
text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use something
> else that will keep the formatting? The text can actuall be over several
> thousand characters.
>
> Thanks
> Charles W
>
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
with
> data.
>
How are you confirmin that the line feeds are actually being removed?
Note that XML will often replace any CRLF sequence with a simple LF.
Could it be that the LFs are there but what you are using to retreive and
display the value requires CRLFs?
Anthony.|||I thought changing the field to nvarchar(4000) worked, but I ran into a size
problem when processing my files. It seems that some of the data is over the
4000 max.
Any other ideas?
Thanks
CW
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741dc7f8c81cab6345f890@.news.microsoft.com...
> Hello Charles,
>
> is nvarchar(max) an option?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:OxLl6xbUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I thought changing the field to nvarchar(4000) worked, but I ran into a
size
> problem when processing my files. It seems that some of the data is over
the
> 4000 max.
> Any other ideas?
>
> Thanks
> CW
>
NText is the field type you need. Still doesn't eliminate the Line feed
issue which as already pointed out is not a function of the SQL data type
you are choosing but is just how XML works.
See:-
http://www.w3.org/TR/REC-xml/#sec-line-ends
Anthony

Data type Conversion during update ...

I am trying to update a row in a table. To my SP, I send an XML string.
One of the columns I am trying to update is of type "Bit" and the value I am
getting from my app is either "true" or "false". This is the general
structure of sp:
UPDATE x
SET x.isEmployee = xmlEmp.isEmployee
WHERE ...
I tried the following to convert my xmlEmp.isEmployee to "BIT" ...
SET x.isEmployee = CASE WHEN xmlEmp.isEmployee='false' THEN 0 ELSE 1 END
SET x.isEmployee = CASE WHEN xmlEmp.isEmployee='false' THEN CAST(0 AS BIT)
ELSE CAST(1 AS BIT) END
both of them don't convert the strings 'false' and 'true' to 0 and 1 ... not
sure what I am missing. Appreciate any tips. TIA.
"exBK" <exBK@.discussions.microsoft.com> wrote in message
news:09146AA0-F234-45F6-9B71-D4F90FDED3B2@.microsoft.com...
>I am trying to update a row in a table. To my SP, I send an XML string.
> One of the columns I am trying to update is of type "Bit" and the value I
> am
> getting from my app is either "true" or "false".
That should work...
declare @.x varchar(10), @.y bit
set @.x = 'true'
set @.y = case when @.x = 'false' then 0 else 1 end
select @.y
The above results in 1. Are you getting an error?
Bryant