How to select every tag from XML document in SQL Server 2008?

sql, sql-server-2008, xml

Solution

You can try something like this:

DECLARE @input XML = '..... add your XML here........'

SELECT 
    NodeName = Nod.value('local-name(.)', 'varchar(50)'),
    NodeValue = Nod.value('.', 'varchar(50)')
FROM @input.nodes('//*') AS TBL(Nod)

This will list all nodes - name and value - in your XML.

WARNING: I just picked `varchar(50)` at random here, as the datatype for the XML node element. If that's not suitable for you - adapt as needed! Since you're converting them all at once, you have to convert them all to the same datatype - and `varchar` seems like a fairly safe choice :-)

Problem

I'm looking if it is possible to make query in T-SQL that will select value from every tag of my XML document. I don't know how many tags there can be, and i don't know their names, because it should be dynamic... It is not important format of output (it can be manipulated later), only thing that is matter is that I get data from every tag :) Thanks

Original source