8ca2a096   
   From: esquel@sommarskog.se   
      
   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;   
   >   
   >   
   >     
   >     A001   
   >     04.09.2012   
   >          
   >            
   >           1   
   >           10   
   >           5.25   
   >            
   >            
   >           1   
   >           10   
   >           5.25   
   >            
   >          
   >     
   >   
   >   
   > 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:   
      
      
        
           
        
      
      
   I also removed the  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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)