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

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

Help for XML data type in ms-sqlserver

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#962 — Help for XML data type in ms-sqlserver

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;)

[toc] | [next] | [standalone]

#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

[toc] | [prev] | [next] | [standalone]

#964

thanks for your explanatory reply

first of all
> 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?

sorry, in my sample xml i had repeated first line by mistake. the
second line there should look like PRODUCTID="2" AMOUNT="5"
PRICE="4.75"

actually ive already done something like that before ive written that
post. (using OPENXML)

two things here disturbing me;

1) .... where/on xxx.DOCUMENTNO=yyy.DOCUMENTNO disturbs me /i dont
know why/

2) my actual purpose here is; what if our xml contains more than one
invoice data?
such as
<ROOT>
<INVOICE>
..nodes for 1st invoice..
</INVOICE>
<INVOICE>
..nodes for 2nd one
</INVOICE>
</ROOT>

or much more complex; what if our xml contains more than one types of
document (such as invoice and purchase details etc)
<ROOT>
<INVOICE>
..nodes for an invoice..
</INVOICE>
<PURCHASE>
..nodes for a purchase document, not related with the previous
invoice..
</INVOICE>
<PURCHASE>
..nodes for an other purchase document..
</PURCHASE>
</INVOICE>
..nodes for a second invoice..
<INVOICE>

etc.

im new to use xml as a datatype in mssql. perhaps im expecting much
from this technique.

thanks again, ur code seems better for a single document

[toc] | [prev] | [next] | [standalone]

#965

Ender Karada? (enkaradag@gmail.com) writes:
> actually ive already done something like that before ive written that
> post. (using OPENXML)

OPENXML is legacy, although it has to be admitted that it sometimes 
performs better than the newer xml type methods. But since the type methods 
does not require the call to sp_xml_prepare/removedocument, I prefer
them over OPENXML.
 
> 1) .... where/on xxx.DOCUMENTNO=yyy.DOCUMENTNO disturbs me /i dont
> know why/
> 
> 2) my actual purpose here is; what if our xml contains more than one
> invoice data?

Guess what? My solution is aimed to handle that situation. And that's 
why get this join that disturbs you. Try it!

-- 
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

[toc] | [prev] | [next] | [standalone]

#966

thanks again,

dont get that "disturbing" on ur solution. i mean ive also used
xxx.DOCUMENTNO=yyy.DOCUMENTNO with my code of OPENXML. and my code
looks disturbing.
document number may not be a unique value (some documents may even
have a null document number) thats why its disturbing me. actually,
this join works for only "inserted" table, so no problem. but that
still makes me disturbing.

also, if i can decide a teqnique for this situation, i may use same
technique in all these master-detail structures. in our project, some
of this kind of master detail structures may have no row for that kind
of join.

/by the way, im not good at english, perhaps im chosing the wrong word
for "disturbing"/

what would ur solution be for a "one shot" insert for more than one
invoice?

what about that?
1) insert multiple rows in invoice table
2) create a temp table for inserted invoice ID values /these ID
numbers have to be kept somewhere/
3) fill the temp table with the inserted rows ID values
4) insert multiple rows in invoicedetail table (with a join btw XML
and #table)

im not sure how to implement that and wats the most efficient solution

how would u design that?

thanks

[toc] | [prev] | [next] | [standalone]

#968

Ender Karada? (enkaradag@gmail.com) writes:
> dont get that "disturbing" on ur solution. i mean ive also used
> xxx.DOCUMENTNO=yyy.DOCUMENTNO with my code of OPENXML. and my code
> looks disturbing.
> document number may not be a unique value (some documents may even
> have a null document number) thats why its disturbing me. actually,
> this join works for only "inserted" table, so no problem. but that
> still makes me disturbing.

You did not say anything, so I had to make a guess. I find it somewhat
problematic - or disturbing :-) that there is no unique in the incoming 
data. How do you protect yourself against duplicates? Or do you trust the 
source?

If there is nothing that uniquely identifies each invoice in the data, this 
makes about impossible to shred the document in the way I suggested. Maybe 
it's possible if you use FLOWR operators in XQuery, but I am not going to 
try that now.

Then again, what version of SQL Server are you using? Does it have to be 
XML?
 
> /by the way, im not good at english, perhaps im chosing the wrong word
> for "disturbing"/

I took "disturbing" to mean that you did not like it for esthetic
reasons.
 

-- 
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

[toc] | [prev] | [next] | [standalone]

#969

thanks for not getting that "disturbing" thing wrong;)

while talking about an invoice, of course we will have some unique
values in the master row. /but document number of an invoice may be a
null or a zero length string also/ but im trying to use this technique
for all master detail record sets. for instance, while saving a "group
list" /we have a just group name for master row, and have some group
details. some distinct groups may have the same group name/ and i have
more than 50 master detail schemas. so this technique may not fail for
dublicate records/
i wrote about a 2 levels document in order to make it simple, but for
instance my real invoice document has 4 levels /invoice master,
invoice rows, invoice row details product list (there may be more than
1 product for an invoice row), invoice rows product list discount
details (there may be unlimited number of discounts for each product
in each rows) etc./

but if i can find a perfect method for 2 levels, developing wont be
problem i think.

to admit, up to now, i was trusting the application code to prevent
invalid data. but now my goal is doing this with sqlserver functions
and removing all the validity controls from the application.

/last week, one of our programmers designed a plugin for our system.
there was a bug and that plugin wrote some invalid rows into orders
and ordersdetails table. thats why i want to move all the saving
procedures into sqlserver/ /after solving this i may also prevent
sqlserver sessions from accepting "INSERT INTO" and "UPDATE"
statements, just accepting "SAVE_XXX_DOCUMENT" functions i decide
here/

we use sqlserver2005 (and 2008 somewhere) now. but may upgrade all db
servers to 2008R2

actually im not locked to xml type for this job. what would your
advice be here? in terms of ease of implementation or working
performance etc.

thank you, regards

[toc] | [prev] | [next] | [standalone]

#970

Ender Karada? (enkaradag@gmail.com) writes:
> we use sqlserver2005 (and 2008 somewhere) now. but may upgrade all db
> servers to 2008R2
> 
> actually im not locked to xml type for this job. what would your
> advice be here? in terms of ease of implementation or working
> performance etc.


Throw SQL 2005 the window and aim at SQL 2008 or SQL 2012. There are a 
couple of new features will help you.

If you use XML, and the XML has enough data to identify each master unique, 
but you don't insert all these fields in the table, you still have a problem 
with mapping the identity values, because you can only list columns you 
inserted in the OUTPUT clause. On SQL 2008 this can be mitigated by using 
the MERGE statement.

Then again, SQL 2008 introduces another features which saves from XML 
altogether: table-valued parameters.

In this context, it is also worth mentioning that SQL 2012 introduces 
sequences, which is a better alternative for surrogate keys over identity 
columns.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#992

Hi again.

im working on the xml subject for a week. ive solved all i think.

thank u for the advices, they worked a lot for me. ive done lots of
work with xml datatypes (and ive got surprised, xml is working faster
than ive expected, also indexes on xmls are working good)

i would be happy if u have a comment about that subject also:

http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/163c4a8e49bb2d37?hl=tr#

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web