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

Question

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

I don't understand what you want here. You just want the name of the elements and don't care about the values? That's what it looks like from your desired results. — Martin Smith
– Martin Smith, Commented Oct 15, 2010 at 22:45
Yeah that is correct. I know it is strange, but I am hoping to use those to give me the ability to do node counts within the XML dynamically. So I can use that information to provide counts on each of the elements in teh XML and then use that in a query. — scarpacci
– scarpacci, Commented Oct 15, 2010 at 22:57
I am trying to figure out how I could build a dynamic call to count the nodes without have to actually build a dynamic query. So if I can expose the counts of each of the elements then I could use that in a where clause. — scarpacci
– scarpacci, Commented Oct 15, 2010 at 22:58
I edited the XML so I would have Person cnt = 1, Age Cnt = 2, Year Cnt = 2, Month Cnt = 2, and day Cnt = 2 then I could do something like where element = 'age' and get back 2 — scarpacci
– scarpacci, Commented Oct 15, 2010 at 23:00

Diadistis · Accepted Answer · 2010-10-15 23:26:28Z

4

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

edited Oct 15, 2010 at 23:26

answered Oct 15, 2010 at 23:07

Diadistis

12.2k1 gold badge35 silver badges57 bronze badges

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

1 Comment

scarpacci Over a year ago

This is money because now I don't have to make th

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related