sql-server - SQL - 解析多列 XML 数据表

Question

我有一个表格，里面装满了我试图解析的 XML 数据。 XML 包含我要解析的多列数据。在某些情况下，将多行 XML 数据填充到单个数据列中，在某些情况下只有一行。样本数据如下:

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

我想要做的是将数据解析为以下 2 列布局

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

以下是我的尝试:

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

任何想法如何解析这个？

澄清:我想在这里继续使用 Native MS SQL SQL。我不想使用任何第三方工具。

Answer 1

试试这个:

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

结果

+----+--------------------------------------+------------+
| 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      |
+----+--------------------------------------+------------+

需要考虑的一些事情:

您的 XML 格式不正确。没有根节点。 SQL-Server 可以处理这样的 XML 片段，但其他用户可能会遇到麻烦。

如果此 XML 在您的控制之下，我会更改设计号以一遍又一遍地存储 C1 值。