Inserting multiple nodes as type xml in SQL

Hello,

I'm working with SQLXML for the first time and need help!

I am working with a customer who passes a CSV from an old accounting system, from which I use XSL to transpose it to a more logical xml document, each row of the CSV matches an item node.

<data>
    <purchase>
         ...
         <order>
         <OrderID>1</OrderID>
             <item>
             ...
             </item>
             <item>
             ...
             </item>
          </order>
          <order>
         <OrderID>2</OrderID>
             ...
          </order>
    </purchase>
</data>

I'm looking to do a single load of the xml document using a stored procedure into an sql table called orders which stores each order node as a seperate row under a xml datatype.

CREATE TABLE [dbo].[Orders](
    [OrderID] [uniqueidentifier] ,      <-extracted from an element under order
    [OrderData] [xml] )

Simplified for ease of describing

Now I could easily do this by C# but I was wondering if this could be done by SQLXML

I'm using SQL 2005 / VS.NET 2005 CTP versions if this helps.

Cheers in advance

Answer this question

Inserting multiple nodes as type xml in SQL

Ruxana Patel
Cheers for that, will give it a try...
Tobi
I had to change your column type from uniqueidentifier to int to fit it to the sample data. The following example should get you started:

CREATE TABLE [dbo].[Orders](
   [OrderID] int , --extracted from an element under order
   [OrderData] [xml] )
go

declare @x xml

set @x = N'<data>
<purchase>
    ...
    <order>
      <OrderID>1</OrderID>
      <item>...</item>
      <item>...</item>
    </order>
    <order>
      <OrderID>2</OrderID>
      ...
    </order>
</purchase>
</data>'

insert into [dbo].[Orders]
select T.o.value('OrderID[1]', 'int'), T.o.query('.')
from @x.nodes('/data/purchase/order') as T(o)

select * from [dbo].[Orders]
Inserting multiple nodes as type xml in SQL

Answer this question