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

Question

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

Answer 1

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 :-)