Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #963

Re: Help for XML data type in ms-sqlserver

Show all headers | View raw

Ender Karada? (enkaradag@gmail.com) writes:
> the common technique is inserting into invoice table first, getting
> the identity ID value for master record, and saving lines with this
> INVOICEID in a loop.

Nah. I would say "was". No one does that any more. Or at least no one 
should.
 
> now i want to design a sqlserver stored procedure with an xml
> parameter, and returning identity ID value in invoice table.

So that is the way to go. Or a table-valued parameter if you are on 
SQL 2008 or later.
 
> my xml should look like that;
> 
><ROOT>
>  <INVOICE>
>     <DOCNUMBER>A001</DOCNUMBER>
>     <DATE>04.09.2012</DATE>
>       <LINES>
>         <LINE>
>           <PRODUCTID>1</PRODUCTID>
>           <AMOUNT>10</AMOUNT>
>           <PRICE>5.25</AMOUNT>
>         </LINE>
>         <LINE>
>           <PRODUCTID>1</PRODUCTID>
>           <AMOUNT>10</AMOUNT>
>           <PRICE>5.25</AMOUNT>
>         </LINE>
>       </LINES>
>  </INVOICE>
></ROOT>
> 
> with one shot, i want to save this schema into database.

Since you say "should", I assume that you have control over the design 
of the document. Attribute-centric XML is easier to deal with:

<ROOT>
  <INVOICE DOCNUMBER="A001" DATE="04.09.2012"
     <LINE PRODUCTID="1" AMOUNT="10" PRICE="5.25" />
     <LINE PRODUCTID="1" AMOUNT="10" PRICE="5.25" />
  </INVOICE>
</ROOT>

I also removed the <LINES> tag, because it serves no purpose.

To insert into the tables do:

DECLARE @t TABLE (ident     int NOT NULL PRIMARY KEY,
                  docnumber nvarchar(30) UNIQUE)

INSERT invoice(docnumber, date)
   OUTPUT inserted.id, inserted.docnumber INTO @t
   SELECT Docnumber = T.c.value('@DOCNUMBER', 'nvarchar(30)'),
          Date      = T.c.value('@DATE', 'datetime')
   FROM   @xml.nodes('/ROOT/INVOICE') AS T(c)

INSERT invoicedetails (invoiceid, product_id, amount, price)
   SELECT t.id, 
          line.c.value('@PRODUCTID', 'int),
          line.c.value('@AMOUT', 'numeric(18,3)'),
          line.c.value('@PRICE', 'numeric(18,2)')
   FROM   @xml.nodes('/ROOT/INVOICE') AS T(c)
   JOIN   @t t ON t.docunumber = T.c.value('@DOCNUMBER', 'nvarchar(30)')
   CROSS  APPLY T.c.nodes('LINE') AS line(c)


By the way, I get an uneasy feeling when I see your table definition of 
invoicedetails and the sample data. What does it mean that two identical 
rows are inserted? Shouldn't (invoiceid, productid) be the primary key?

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Help for XML data type in ms-sqlserver Ender Karadağ <enkaradag@gmail.com> - 2012-04-09 10:35 -0700
  Re: Help for XML data type in ms-sqlserver Erland Sommarskog <esquel@sommarskog.se> - 2012-04-09 21:22 +0200
    Re: Help for XML data type in ms-sqlserver Ender Karadağ <enkaradag@gmail.com> - 2012-04-09 13:32 -0700
      Re: Help for XML data type in ms-sqlserver Erland Sommarskog <esquel@sommarskog.se> - 2012-04-09 23:31 +0200
        Re: Help for XML data type in ms-sqlserver Ender Karadağ <enkaradag@gmail.com> - 2012-04-09 14:59 -0700
          Re: Help for XML data type in ms-sqlserver Erland Sommarskog <esquel@sommarskog.se> - 2012-04-10 22:14 +0200
            Re: Help for XML data type in ms-sqlserver Ender Karadağ <enkaradag@gmail.com> - 2012-04-10 15:40 -0700
              Re: Help for XML data type in ms-sqlserver Erland Sommarskog <esquel@sommarskog.se> - 2012-04-11 07:34 +0000
                Re: Help for XML data type in ms-sqlserver Ender Karadağ <enkaradag@gmail.com> - 2012-04-19 10:51 -0700

csiph-web