CODEWITHSUNDEEP
sql-serverxmlxpath
Matt
Matt

Reputation: 195

How do I get the child node values AND parent node values in SQL XPath

My data (passed into a parameter (@Data XML) in a Stored Procedure) looks like this:

<Records>
    <Record id="1">
        <Data>
            <FirstName>John</FirstName>
            <LastName>Doe</LastName>
        </Data>
        <Result>
            <StatusId>3</StatusId>
            <ErrorCodes>
                <Item>4</Item>
                <Item>23</Item>
                <Item>19</Item>
            </ErrorCodes>
        </Result>
    </Record>
   <Record id="2">
        <Data>
            <FirstName>Fred</FirstName>
            <LastName>Blog</LastName>
        </Data>
        <Result>
            <StatusId>2</StatusId>
            <ErrorCodes>
                <Item>1</Item>
                <Item>3</Item>
            </ErrorCodes>
        </Result>
    </Record>
</Records>

I want to select the Record id and the Error Codes, like this:

id    Item
----------
 1       4
 1      23
 1      19
 2       1
 2       3

The order of data doesn't matter.

The following gets me the Error Codes, but not the Record id:

SELECT Data.value('.', 'int') as ErrorCode
FROM @Data.nodes('/Records/Record/Result/ErrorCodes/*') AS data(Data)

Upvotes: 3

Views: 4601

Answers (1)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

This expression should get you parent-of-parent-of-parent element:

Data.query('../../..')

...so try something like this (untested)...

SELECT
  id = Data.value('../../../@id', 'int'),
  item = Data.value('.', 'int')
FROM @Data.nodes('/Records/Record/Result/ErrorCodes/*') AS data(Data)

Upvotes: 3

Related Questions