Select all XML nodes from XML column

Question

I have a table with an XML column. The XML structure of each entry is completely flat, without even the parent tag - this is an example of one entry:

<tag1>1.22</tag1>
<tag3>5</tag3>
<tag12>-1.22</tag>

So far, I've been able to do things like this:

SELECT CAST(xml_column AS NVARCHAR(MAX)) as XML_text

And parse the XML on my end. Or I apparently I can write xpath queries to select tags into columns, which is what I want, except there is like 1000 possible tag names, and I don't want to write them all out (and possibly miss one).

In short, how do I go from this:

| ID | XML type column                |
| 1  | <tag1>1</tag1><tag2>2</tag2>   |
| 2  | <tag2>8</tag2><tag34>1</tag34> |

To this:

| ID | tag1 | tag2 | tag34 |
| 1  | 1    | 2    | NULL  |
| 2  | NULL | 8    | 1     |

for any tags I could find in my dataset, without knowing them in advance? I would settle for this as well:

| ID | tag   | value | 
| 1  | tag1  | 1     |
| 1  | tag2  | 2     |
| 2  | tag2  | 8     |
| 2  | tag34 | 1     |

Answer 1

The following will transform your flat XML without a root node into a classical EAV list:

DECLARE @tbl TABLE(ID INT IDENTITY, YourXml XML);
INSERT INTO @tbl VALUES
 ('<tag1>1</tag1><tag2>2</tag2>')
,('<tag3>3</tag3><tag4>4</tag4><tag5>5</tag5>');

--The query

SELECT t.ID
      ,AllNodes.value('local-name(.)','nvarchar(max)') AS TagName
      ,AllNodes.value('text()[1]','nvarchar(max)') AS TagValue
FROM @tbl t
CROSS APPLY t.YourXml.nodes('/*') A(AllNodes);

The XQuery function local-name() allows to query for meta data, the text() node represents the element's content.