如何解析 SQL Server 表中的 XML 数据

Question

我有 SQL 表：

 Create table
(
ID varchar(50) not null,
Action nvarchar(max) null
)

操作列包含 XML 数据。格式：

 <?xml version="1.0" encoding="UTF-8"?>
<oo_outbound_order>
         <oo_master>
              <Code>123</Code>
              <Name>Branan</Name>
    </oo_master>
    </oo_outbound_order>

如何解析此列？结果应该是这样的：

 CODE    NAME
123     Branan

原文由 Kirill Rodeonov 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

将 XML 数据存储在字符串列中是一个非常糟糕的主意。

更糟糕的是，您正在存储一个字符串，该字符串声称 我被编码为 utf-8 ！ ，但它存储在 NVARCHAR 的列中，即 UCS-2 （几乎与 utf-16 相同）。

这意味着，您必须先修复它并将字符串转换为 XML，然后才能使用它。每当你抓住它时，你都必须做这些昂贵的操作。如果可以更改此设置，则应将 XML 存储在本机类型的列中。

尽管如此，这是可以做到的。尝试这个。

 Create table #temp
(
ID varchar(50) not null,
[Action] nvarchar(max) null
)

INSERT INTO #temp(ID,[Action]) VALUES
('test 1',
'<?xml version="1.0" encoding="UTF-8"?>
<oo_outbound_order>
         <oo_master>
              <Code>123</Code>
              <Name>Branan</Name>
    </oo_master>
    </oo_outbound_order>');

SELECT t.ID
      ,ActionXml.value('(/oo_outbound_order/oo_master/Code/text())[1]','nvarchar(max)') AS CODE
      ,ActionXml.value('(/oo_outbound_order/oo_master/Name/text())[1]','nvarchar(max)') AS [Name]
FROM #temp t
CROSS APPLY(SELECT CAST(REPLACE(t.[Action],'encoding="UTF-8"','encoding="UTF-16"') AS XML) ActionXml) A;

drop table #temp;

提示：如果没有重复元素，则不需要 .nodes() 其他答案建议…

原文由 Shnugo 发布，翻译遵循 CC BY-SA 4.0 许可协议