Select XML element in SQL Server

Question

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?

+1 for easily testable/reproducible example

Adam Wenger
– Adam Wenger

2012-10-18 21:28:01 +00:00
Commented Oct 18, 2012 at 21:28 — Adam Wenger
– Adam Wenger, Commented Oct 18, 2012 at 21:28

Adam Wenger · Accepted Answer · 2012-10-18 21:48:13Z

6

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

edited Oct 18, 2012 at 21:48

answered Oct 18, 2012 at 21:34

Adam Wenger

17.7k8 gold badges55 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jared Over a year ago

Thanks Adam for the guidance and external link.

Mike Gledhill · Accepted Answer · 2016-09-09 06:57:20Z

0

The following SQL would also work nicely with your data:

--  Iterate through each of the "ROOT\InvoiceDetail" records in our XML
SELECT 
    x.Rec.query('./OrderId').value('.', 'nvarchar(2000)') AS 'OrderID',
    x.Rec.query('./Id').value('.', 'nvarchar(2000)') AS 'ID',
    items.cd.query('./Cd').value('.', 'nvarchar(2000)') AS 'ItemID'
FROM @doc.nodes('/ROOT/InvoiceDetail') as x(Rec)
CROSS APPLY x.Rec.nodes('./InvoiceItems/InvoiceItem') Items(Cd)

This gives us these results:

(The syntax is baffling though, isn't it !)

answered Sep 9, 2016 at 6:57

Mike Gledhill

29.4k8 gold badges160 silver badges163 bronze badges

Collectives™ on Stack Overflow

Select XML element in SQL Server

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related