Select all XML nodes from XML column

user_7953924 0
Viewed 3698

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     |
xquery sql-server-2016 xml tsql sql-server
2 Answers

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.

user_50892040

It is relatively simple to do in MS SQL Server by using XML data type methods and XQuery. SQL Server can handle XML fragments without a root element without any problem.

What you are trying to achieve is called XML shredding. Though you do need to know (1) XML element names or (2) their sequential position.

Check it out method #1.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<tag1>1</tag1><tag2>2</tag2>')
,(N'<tag2>8</tag2><tag34>1</tag34>');
-- DDL and sample data population, end

-- Shred XML and convert it into a rectangular format
SELECT ID
    , col.value('(tag1/text())[1]','VARCHAR(10)') AS tag1 
    , col.value('(tag2/text())[1]','VARCHAR(10)') AS tag2
    , col.value('(tag3/text())[1]','VARCHAR(10)') AS tag3
    , col.value('(tag34/text())[1]','VARCHAR(10)') AS tag34
FROM @tbl AS tbl
    CROSS APPLY tbl.xmldata.nodes('.') AS tab(col);

Output

+----+------+------+------+-------+
| ID | tag1 | tag2 | tag3 | tag34 |
+----+------+------+------+-------+
|  1 | 1    |    2 | NULL | NULL  |
|  2 | NULL |    8 | NULL | 1     |
+----+------+------+------+-------+
user_19323110
Related