SQL - Parse table of multi-column XML Data

Question

I have a table filled with XML Data that I am trying to parse. The XML contains multiple columns of data that I am trying to parse. In some cases there are multiple rows of XML data stuffed into the single column of data and in some cases just one. sample data below:

<REC><C1>0E5627DF-DBB1-4300-40F2-715A8C96190B</C1><C2>apples</C2></REC>
<REC><C1>59868DA4-DB9D-1384-B07D-715A8C96197B</C1><C2>oranges</C2></REC><REC><C1>59868DA4-DB9D-1384-B07D-715A8C96197B</C1><C2>grapes</C2></REC><REC><C1>59868DA4-DB9D-1384-B07D-715A8C96197B</C1><C2>apples</C2></REC>
<REC><C1>7FB8C203-DB30-5340-B07D-715A8C9619FA</C1><C2>bananas</C2></REC><REC><C1>7FB8C203-DB30-5340-B07D-715A8C9619FA</C1><C2>watermelon</C2></REC><REC><C1>7FB8C203-DB30-5340-B07D-715A8C9619FA</C1><C2>limes</C2></REC>
<REC><C1>38B13BFB-DBAA-C340-40F2-715A8C961942</C1><C2>apples</C2></REC>
<REC><C1>58209738-DB3C-DB00-D01A-7FDA8C9619B5</C1><C2>pears</C2></REC><REC><C1>58209738-DB3C-DB00-D01A-7FDA8C9619B5</C1><C2>limes</C2></REC>

What I am trying to do is parse the data into the following 2 column layout

C1                                      C2
0E5627DF-DBB1-4300-40F2-715A8C96190B    apples
59868DA4-DB9D-1384-B07D-715A8C96197B    oranges
59868DA4-DB9D-1384-B07D-715A8C96197B    grapes
59868DA4-DB9D-1384-B07D-715A8C96197B    apples
7FB8C203-DB30-5340-B07D-715A8C9619FA    bananas
7FB8C203-DB30-5340-B07D-715A8C9619FA    watermelon
7FB8C203-DB30-5340-B07D-715A8C9619FA    limes
38B13BFB-DBAA-C340-40F2-715A8C961942    apples
58209738-DB3C-DB00-D01A-7FDA8C9619B5    pears
58209738-DB3C-DB00-D01A-7FDA8C9619B5    limes

Below is my attempt at it:

SELECT Split.XMLD.value('.', 'VARCHAR(500)')
FROM myTable XMLD
CROSS APPLY XMLD.REC.nodes ('/REC') AS Split(XMLD)

Any ideas how to parse this?

Clarification: I want to stay with Native MS SQL SQL here. I don't want to use any third party tools.

Gottfried Lesigang · Accepted Answer · 2018-04-09 19:48:53Z

Try this:

DECLARE @mockupTable TABLE (ID INT IDENTITY, YourXml XML);
INSERT INTO @mockupTable VALUES
 ('<REC><C1>0E5627DF-DBB1-4300-40F2-715A8C96190B</C1><C2>apples</C2></REC>')
,('<REC><C1>59868DA4-DB9D-1384-B07D-715A8C96197B</C1><C2>oranges</C2></REC><REC><C1>59868DA4-DB9D-1384-B07D-715A8C96197B</C1><C2>grapes</C2></REC><REC><C1>59868DA4-DB9D-1384-B07D-715A8C96197B</C1><C2>apples</C2></REC>')
,('<REC><C1>7FB8C203-DB30-5340-B07D-715A8C9619FA</C1><C2>bananas</C2></REC><REC><C1>7FB8C203-DB30-5340-B07D-715A8C9619FA</C1><C2>watermelon</C2></REC><REC><C1>7FB8C203-DB30-5340-B07D-715A8C9619FA</C1><C2>limes</C2></REC>')
,('<REC><C1>38B13BFB-DBAA-C340-40F2-715A8C961942</C1><C2>apples</C2></REC>')
,('<REC><C1>58209738-DB3C-DB00-D01A-7FDA8C9619B5</C1><C2>pears</C2></REC><REC><C1>58209738-DB3C-DB00-D01A-7FDA8C9619B5</C1><C2>limes</C2></REC>');

SELECT ID
      ,r.value(N'(C1/text())[1]','uniqueidentifier') AS C1
      ,r.value(N'(C2/text())[1]','nvarchar(max)') AS C2
FROM @mockupTable AS t
CROSS APPLY t.YourXml.nodes(N'/REC') AS A(r) ;

The result

+----+--------------------------------------+------------+
| ID | C1                                   | C2         |
+----+--------------------------------------+------------+
| 1  | 0E5627DF-DBB1-4300-40F2-715A8C96190B | apples     |
+----+--------------------------------------+------------+
| 2  | 59868DA4-DB9D-1384-B07D-715A8C96197B | oranges    |
+----+--------------------------------------+------------+
| 2  | 59868DA4-DB9D-1384-B07D-715A8C96197B | grapes     |
+----+--------------------------------------+------------+
| 2  | 59868DA4-DB9D-1384-B07D-715A8C96197B | apples     |
+----+--------------------------------------+------------+
| 3  | 7FB8C203-DB30-5340-B07D-715A8C9619FA | bananas    |
+----+--------------------------------------+------------+
| 3  | 7FB8C203-DB30-5340-B07D-715A8C9619FA | watermelon |
+----+--------------------------------------+------------+
| 3  | 7FB8C203-DB30-5340-B07D-715A8C9619FA | limes      |
+----+--------------------------------------+------------+
| 4  | 38B13BFB-DBAA-C340-40F2-715A8C961942 | apples     |
+----+--------------------------------------+------------+
| 5  | 58209738-DB3C-DB00-D01A-7FDA8C9619B5 | pears      |
+----+--------------------------------------+------------+
| 5  | 58209738-DB3C-DB00-D01A-7FDA8C9619B5 | limes      |
+----+--------------------------------------+------------+

Some things to think about:

Your XML is not well-formed. There is no root-node. SQL-Server can deal with such XML fragments, but other comsumers might get in troubles.
If this XML is under your control I'd change the design no to store the C1 value over and over.

I agree with the non-well formed info, sadly I don't have any sway to control the source.

SQLHTTP · Accepted Answer · 2018-04-14 03:17:01Z

Here is a really neat way to easily generate the XQuery/XPath query for any XML data no matter the complexity or "ugliness":

It requires SQLHTTP which is a free database/assembly that we created which you can find on our website at: http://sqlhttp.net/documentation/xqueryhelper

First you need to set an XML variable with your data. Notice that I added a opening tag and closing tag.

DECLARE @X xml = '<ROOT>
    <REC><C1>0E5627DF-DBB1-4300-40F2-715A8C96190B</C1><C2>apples</C2></REC>
<REC><C1>59868DA4-DB9D-1384-B07D-715A8C96197B</C1><C2>oranges</C2></REC><REC><C1>59868DA4-DB9D-1384-B07D-715A8C96197B</C1><C2>grapes</C2></REC><REC><C1>59868DA4-DB9D-1384-B07D-715A8C96197B</C1><C2>apples</C2></REC>
<REC><C1>7FB8C203-DB30-5340-B07D-715A8C9619FA</C1><C2>bananas</C2></REC><REC><C1>7FB8C203-DB30-5340-B07D-715A8C9619FA</C1><C2>watermelon</C2></REC><REC><C1>7FB8C203-DB30-5340-B07D-715A8C9619FA</C1><C2>limes</C2></REC>
<REC><C1>38B13BFB-DBAA-C340-40F2-715A8C961942</C1><C2>apples</C2></REC>
<REC><C1>58209738-DB3C-DB00-D01A-7FDA8C9619B5</C1><C2>pears</C2></REC><REC><C1>58209738-DB3C-DB00-D01A-7FDA8C9619B5</C1><C2>limes</C2></REC>
</ROOT>'

You then execute the following stored procedure:

EXEC SQLHTTP.net.XqueryHelper @X

In the case, the procedure will output the following four lines:

Usage                                             Name   Rows
------------------------------------------------- ------ ------
EXEC SQLHTTP.net.XQueryHelper @X, 'ROOT'          ROOT   1
EXEC SQLHTTP.net.XQueryHelper @X, 'ROOT/REC'      REC    10
EXEC SQLHTTP.net.XQueryHelper @X, 'ROOT/REC/C1'   C1     10
EXEC SQLHTTP.net.XQueryHelper @X, 'ROOT/REC/C2'   C2     10

The line you're interested in to get you the ten records with the fruit names is the second line:

EXEC SQLHTTP.net.XQueryHelper @X, 'ROOT/REC'

The above stored procedure call will then output your XQuery/XPath like this:

SELECT  T.C.value(N'C1[1]', N'nvarchar(MAX)') AS [C1]
    ,T.C.value(N'C2[1]', N'nvarchar(MAX)') AS [C2]
FROM @X.nodes(N'/ROOT/REC') T(C)

Collectives™ on Stack Overflow

SQL - Parse table of multi-column XML Data

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related