CODEWITHSUNDEEP
sql-serverxmlt-sqlxpathxquery
Dave
Dave

Reputation: 1961

SQL - Parse table of multi-column XML Data

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.

Upvotes: 1

Views: 201

Answers (2)

SQLHTTP
SQLHTTP

Reputation: 47

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)

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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.

Upvotes: 2

Related Questions