How can I get distinct xml nodes using query in SQL Server 2008

Question

Hi All,

I have a scenario where I want to get the distinct nodes from XML.

So if I have this XML:

<person>
<age>
    <year value="2010"/>
    <month value="10"/>
    <day value="21"/>
</age>
<age>
    <year value="2011"/>
    <month value="11"/>
    <day value="4"/>
</age>
</person>

How could I retrieve in the results:

person
age
year
month
day

Is this possible? I was playing around with nodes.query and nodes.value, but I couldn't seem to figure out how to extract the actual node values?

Thanks,

S

Answer 1

DECLARE @person XML
SELECT @person = CAST('
    <person>
        <age>
            <year value="2010"/>
            <month value="10"/>
            <day value="21"/>
        </age>
        <age>
            <year value="2011"/>
            <month value="11"/>
            <day value="4"/>
        </age>
    </person>' AS XML)

;WITH nodeData AS (
    SELECT 
        node.value('local-name(.)', 'NVARCHAR(MAX)') AS nodeName,
        node.query('.') AS nodeInstance
    FROM @person.nodes('/*') a(node)
    UNION ALL
    SELECT 
        node.value('local-name(.)', 'NVARCHAR(MAX)'),
        node.query('.')
    FROM nodeData
    CROSS APPLY nodeInstance.nodes('/*/*') b(node)
)
SELECT nodeName, COUNT(nodeName) AS nodeCount FROM nodeData
GROUP BY nodeName 
ORDER BY nodeCount DESC