XQuery parent and child

Question

Example:

DECLARE @XML XML = '
<Items>
    <document id="doc1" value="100">
        <details>
            <detail detailID="1" detailValue="20"/>
            <detail detailID="2" detailValue="80"/>
        </details>
    </document>
    <document id="doc2" value="0">
        <details>
        </details>
    </document>
</Items>
'

I want results like this:

id    value    detailID   detailValue
doc1  100      1          20
doc1  100      2          80
doc2  0        NULL       NULL

Tried:

SELECT document.value('../../@docID', 'VARCHAR(10)') AS 'docID',
       document.value('../../@value', 'INT') AS 'value',
       document.value('@detailID', 'VARCHAR(10)') AS 'detailID',
       document.value('@detailValue', 'INT') AS 'detailValue'
FROM   @XML.nodes('Items/document/details/detail') AS Documents(document)

But, doc2 is not listed... Also, tried with CROSS JOIN and INNER JOIN, but performance is very bad.

score 4 · Answer 1 · answered Mar 28 '13 at 18:03

4

Try this:

SELECT document.value('@id', 'VARCHAR(10)') AS docID,
       document.value('@value', 'INT') AS value,
       Detail.value('@detailID', 'INT') as DetailId,
       Detail.value('@detailValue', 'INT') as DetailValue
FROM   @XML.nodes('Items/document') AS Documents(document)
       outer apply Documents.document.nodes('details/detail') as Details(Detail);

answered Mar 28 '13 at 18:03

Kevin Suchlicki

3,096
2
15
17

You're welcome. But performance is horrible? You must be doing this on some very large XML docs (or else a whole lot of records). Good luck! – Kevin Suchlicki Mar 28 '13 at 19:07
Not really (3 docs app.) ... The problem is deep... with your method, I would have to do 3 outer apply... 3 docs = response in 6 seconds... – Ragys Mar 28 '13 at 19:35

score 1 · Answer 2 · answered Jun 19 '14 at 08:46

1

Just one added detail:

@XML.nodes('//whatever_depth') AS Documents(document)

Using '//' Allows you to query not directly from root

Regards, Dennes

answered Jun 19 '14 at 08:46

user3755645

11
1

XQuery parent and child

2 Answers2