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

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

Help for XML data type in ms-sqlserver

Show all headers | View raw

Hi guys,

ive searched net but could not find a suitable technique

assume i have two tables;

table : invoice
ID bigint identity
DOCNUMBER nvarchar(30)
DATE datetime

table : invoicedetails
ID bigint identity
PRODUCTID bigint (assume we have a product table, and this field is FK
to product.ID
AMOUNT numeric(18,3)
PRICE numeric (18,2)
INVOICEID bigint (FK to invoice.ID)

i am trying to save an invoice (lets say DOCNUMBER: A001, date :
04.09.2012 with two lines PRODUCTID=1, AMOUNT=10, PRICE=5.25,
PRODUCTID=2, AMOUNT=5, PRICE=4.75)

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.

now i want to design a sqlserver stored procedure with an xml
parameter, and returning identity ID value in invoice table.

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.

dbo.proc_saveinvoice(xmlinvoice:xml)

any idea?

Thanks for all;)

Back to comp.databases.ms-sqlserver | Previous | Next — 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