|
Home > Archive > MS SQL XML > January 2006 > XQuery to create new xml doc...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
XQuery to create new xml doc...
|
|
| Chris Kilmer 2006-01-19, 8:24 pm |
| I am using Sql Server 2005 and it's XQuery capabilities to shred xml
docs into relational tables. Here is a sample doc:
<Invoice>
<InvoiceID></InvoiceID>
<Customer>
<FName></FName>
<LName></LName>
<Address>
<Street></Street>
<City></City>
<Zip></Zip>
</Address>
</Customer>
</Invoice>
I have separate tables for Invoice, Customer and Address.
Currently, I have a single sproc that accepts the xml doc and then
shreds the data into the 3 appropriate tables. However, I would like
to create 3 separate procs for shredding the invoice.
Proc #1 would shred <InvoiceID/> into the invoice table. Next, proc #1
would create a new xml doc with just the Customer info and send the new
xml doc to proc #2. The new xml doc would look like this:
<Customer>
<FName></FName>
<LName></LName>
<Address>
<Street></Street>
<City></City>
<Zip></Zip>
</Address>
</Customer>
Proc #2 would shred <FName/> and </LName> into the Customer table.
Next, proc #2 would create a new xml doc with just the Address info and
then send this new xml doc to sproc #3 for shredding.
The idea is simple. However, I do not understand how to retreive the
Customer node from the Invoice xml as a new xml doc and then send the
new xml doc to the next sproc.
I need to be able to save Customer, Invoice and Address data
separately, so I would love to be able to move from one all
encompassing sproc to a more modular approach. Any help would be
appreciated.
BTW, I am not using OPENXML. Rather, I am using the nodes() method.
For example:
DECLARE @Invoice xml
SET @Invoice = '...some xml...'
INSERT INTO Invoices
(InvoiceID)
SELECT @Invoice.value('number(*[1]/InvoiceID[1])', 'INT')
| |
|
|
|
|
|