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

Ender Karada? (enkaradag (AT) gmail (DOT) com) writes:
Nah. I would say "was". No one does that any more. Or at least no one
should.

So that is the way to go. Or a table-valued parameter if you are on
SQL 2008 or later.

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 (AT) sommarskog (DOT) 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

thanks for your explanatory reply

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

Ender Karada? (enkaradag (AT) gmail (DOT) com) writes:
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.

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 (AT) sommarskog (DOT) 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

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

Ender Karada? (enkaradag (AT) gmail (DOT) com) writes:
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?

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


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

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

Ender Karada? (enkaradag (AT) gmail (DOT) com) writes:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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....9bb2d37?hl=tr#