CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2005t-sql
muek
muek

Reputation: 1080

Get XML Node while using tsql

I would like to know, if anyone can help how to get NODE name o XML variable using TSQL.

<ROOT>
<IDS>
<ID>
<NAME>bla1</NAME>
<AGE>25</AGE>
</ID>
<ID>
<NAME>bla2</NAME>
<AGE>26</AGE>
</ID>
</IDS>
</ROOT>

After my query, I should be able to get the nodes names: NAME, AGE

My SQL server is MSSQL 2005.

Upvotes: 0

Views: 1413

Answers (1)

Cameron Jordan
Cameron Jordan

Reputation: 759

This would give you the node names for the children of the first ID node:

DECLARE @x xml

SET @x = '<ROOT>
<IDS>
<ID>
<NAME>bla1</NAME>
<AGE>25</AGE>
</ID>
<ID>
<NAME>bla2</NAME>
<AGE>26</AGE>
</ID>
</IDS>
</ROOT>'

SELECT T.c.value('local-name(.)', 'varchar(50)') 
FROM @x.nodes('/ROOT/IDS/ID[1]/*') T(c)

Upvotes: 1

Related Questions