Menu
I have some XML that I need to parse using SQL Server 2008. I think I'm close to getting what I want, but I don't have the correct syntax (I believe).
I have the following:
DECLARE @doc XML
SET @doc = '<ROOT>
<InvoiceDetail>
<OrderId>1000000</OrderId>
<OrderTypeId>2</OrderTypeId>
<Id>2000</Id>
<InvoiceItems>
<InvoiceItem>
<LineId>1</LineId>
<Cd>123456</Cd>
<Description>Item 1</Description>
<Quantity>1</Quantity>
<UnitPrice>99.990000</UnitPrice>
</InvoiceItem>
<InvoiceItem>
<LineId>2</LineId>
<Cd>234567</Cd>
<Description>Item 2</Description>
<Quantity>1</Quantity>
<UnitPrice>89.990000</UnitPrice>
</InvoiceItem>
</InvoiceItems>
</InvoiceDetail>
<InvoiceDetail>
<OrderId>1200000</OrderId>
<OrderTypeId>1</OrderTypeId>
<Id>3000</Id>
<InvoiceItems>
<InvoiceItem>
<LineId>1</LineId>
<Cd>234567</Cd>
<Description>Item 2</Description>
<Quantity>1</Quantity>
<UnitPrice>89.990000</UnitPrice>
</InvoiceItem>
<InvoiceItem>
<LineId>2</LineId>
<Cd>345678</Cd>
<Description>Item 3</Description>
<Quantity>1</Quantity>
<UnitPrice>79.990000</UnitPrice>
</InvoiceItem>
</InvoiceItems>
</InvoiceDetail>
</ROOT>'
SELECT
Invoices.Node.value('@OrderId', 'VARCHAR(10)') 'OrderID'
, Invoices.Node.value('@Id', 'INT') 'InvoiceId'
, Items.Cd.value('.', 'VARCHAR(14)') 'ItemId'
FROM
@doc.nodes('//InvoiceDetail') Invoices(Node)
CROSS APPLY Invoices.Node.nodes('./InvoiceItems/InvoiceItem/Cd') Items(Cd)
I get the following results:
NULL NULL 123456
NULL NULL 234567
NULL NULL 234567
NULL NULL 345678
I'm trying to get the following:
1000000 2000 123456
1000000 2000 234567
1200000 3000 234567
1200000 3000 345678
What am I doing wrong?
231
SQL Server provides the XQuery feature to querying XML data type or querying with the XML column with the XPATH. Using XQuery, users can Insert, Update and Delete with the XML nodes and node values in an XML column.
You should use the query() Method if you want to get a part of your XML. If you want the value from a specific node you should use value() Method. Update: If you want to shred your XML to multiple rows you use nodes() Method.
To retrieve data in XML format from SQL Server database, we can use FOR XML <options> clause. Notice the last three words in the above query. We have a normal SELECT statement and in the last of the statement, we are suffixing FOR XML RAW that will return the data from PersonalDetails table in Raw xml format.
You can retrieve multiple values from the rowset. For example, you can apply the value() method to the rowset returned by nodes() and retrieve multiple values from the original XML instance. The value() method, when applied to the XML instance, returns only one value.
The Syntax for grabbing an element is:
SELECT Invoices.Node.value('(OrderId)[1]', 'VARCHAR(10)') 'OrderID'
, Invoices.Node.value('(Id)[1]', 'INT') 'InvoiceId'
, Items.Cd.value('.', 'VARCHAR(14)') 'ItemId'
FROM
@doc.nodes('//InvoiceDetail') Invoices(Node)
CROSS APPLY Invoices.Node.nodes('./InvoiceItems/InvoiceItem/Cd') Items(Cd)
This also appears to work without the explicit parenthesis:
Invoices.Node.value('OrderId[1]', 'VARCHAR(10)')
The @ syntax is for attributes, not elements in XQuery. If you had
<InvoiceDetail title="something">
Then you would be able to query that using:
SELECT Invoices.Node.value('@title', 'VARCHAR(MAX)') AS Title
FROM @doc.nodes('//InvoiceDetail') Invoices(Node)
Here is a good article about using XQuery value
76
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
