Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Help for XML data type in ms-sqlserver
Date: Mon, 09 Apr 2012 21:22:08 +0200
Organization: Erland Sommarskog
Lines: 81
Message-ID: <XnsA030D96067AF5Yazorman@127.0.0.1>
References: <19738bce-58dd-477f-a1f2-a87edd2064b7@p6g2000yqi.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: mx04.eternal-september.org; posting-host="nBFDv6s1VJQDuF1w6hpX2A"; logging-data="19202"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19chnLGmXAtaoJr7Gk51M7/"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:GHicUi5sHRDIwCPdIM32JP1wqRM=
Xref: csiph.com comp.databases.ms-sqlserver:963

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