解析存储多列XML数据的SQL表：单列含多行/单行XML处理

嘿，我来帮你搞定这个XML解析的问题！不管你的SQL表列里存的是单个节点，还是一堆连在一起的多行XML，都可以用SQL Server的XQuery功能轻松搞定，下面给你一步步演示：

1. 先模拟你的测试场景 #

假设你的表叫XmlTable，存XML的列是XmlData，先建个测试表插点示例数据，和你给的格式一致：

CREATE TABLE XmlTable (ID INT, XmlData NVARCHAR(MAX));
INSERT INTO XmlTable VALUES
(1, '<REC><C1>0E5627DF-DBB1-4300-40F2-715A8C96190B</C1><C2>apples</C2></REC>'),
(2, '<REC><C1>59868DA4-DB9D-1384-B07D-715A8C96197B</C1><C2>oranges</C2></REC><REC><C1>12345678-ABCD-1234-EFGH-9876543210AB</C1><C2>bananas</C2></REC>');

2. 核心解析语句（推荐用这个！） #

用nodes()方法把每个拆成单独的行，再用value()提取里面的C1、C2值，一行SQL就能搞定所有情况：

SELECT
    t.ID, -- 原表的行ID，用来对应原数据
    rec.value('(C1/text())[1]', 'UNIQUEIDENTIFIER') AS C1, -- 提取C1，因为是GUID所以指定类型
    rec.value('(C2/text())[1]', 'NVARCHAR(50)') AS C2 -- 提取C2，水果名用字符串类型
FROM XmlTable t
CROSS APPLY t.XmlData.nodes('/REC') AS XmlNodes(rec);

小说明： #

• nodes('/REC')：把列里的每个节点单独拆出来，变成一行行的数据
• CROSS APPLY：如果某行的XML里没有或者是空的，就不会返回这行；要是想保留所有原表行（哪怕XML无效），换成OUTER APPLY就行
• value()里的(C1/text())[1]是为了精准提取节点文本，避免可能的嵌套问题

3. 处理可能的坑：无效XML #

如果你的表里有些行的XML格式有问题（比如乱码、标签不闭合），可以用TRY_CONVERT来跳过这些无效行，避免整个查询报错：

SELECT
    t.ID,
    rec.value('(C1/text())[1]', 'UNIQUEIDENTIFIER') AS C1,
    rec.value('(C2/text())[1]', 'NVARCHAR(50)') AS C2
FROM XmlTable t
CROSS APPLY TRY_CONVERT(XML, t.XmlData).nodes('/REC') AS XmlNodes(rec)
WHERE TRY_CONVERT(XML, t.XmlData) IS NOT NULL; -- 只保留能转成XML的行

4. 旧版本SQL Server的替代方案：OPENXML #

如果你的SQL Server版本比较老（比如2008及以前），可以用OPENXML的方式，但这个需要逐行处理XML，效率不如上面的XQuery，给你参考下：

DECLARE @Xml XML;
DECLARE @DocHandle INT;

-- 先取一行多行XML的例子
SELECT @Xml = XmlData FROM XmlTable WHERE ID = 2;

-- 初始化XML文档句柄
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @Xml;

-- 提取数据
SELECT *
FROM OPENXML(@DocHandle, '/REC', 2)
WITH (
    C1 UNIQUEIDENTIFIER 'C1',
    C2 NVARCHAR(50) 'C2'
);

-- 记得释放句柄，避免内存泄漏
EXEC sp_xml_removedocument @DocHandle;

上面的方法不管你的XML是单行还是一堆连在一起，都能正确把每个里的C1、C2提取成关系型数据，直接用就行～

内容的提问来源于stack exchange，提问作者Dave