SQL Server 2016中用nodes/value提取XML的Org值返回NULL求助

嘿，问题很明确啦——你的XML文档包含了默认命名空间，但你的T-SQL查询完全没处理这个，这就是Rater列一直返回NULL的核心原因！

先看你的XML根元素：

<ACORD xmlns="http://www.ACORD.org/standards/PC_Surety/ACORD1.11.0/xml/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

这里的xmlns定义了整个XML的默认命名空间，所有子元素（包括<Org>）都属于这个命名空间。SQL Server的XML查询是严格区分命名空间的，如果你不声明并使用这个命名空间，查询引擎会去寻找没有命名空间的元素，自然找不到匹配的内容，所以返回NULL。

下面是修改后的两种查询写法：

1. 使用nodes()方法的修正版 #

需要先通过WITH XMLNAMESPACES声明默认命名空间，然后在XPath中使用对应的元素路径：

WITH XMLNAMESPACES(DEFAULT 'http://www.ACORD.org/standards/PC_Surety/ACORD1.11.0/xml/')
SELECT * 
FROM ( 
    SELECT rtr.BridgedDate, 
           rtr.RealTimeRequestSysId, 
           rtr.QuoteId, 
           rtr.AgentId, 
           rtr.XmlRequest, 
           x.m.value('(Org)[1]', 'varchar(max)') as Rater 
    FROM dbo.AUT_RealTimeRequest rtr 
    OUTER APPLY rtr.XmlRequest.nodes('ACORD/SignonRq/ClientApp') x(m) 
    WHERE rtr.BridgedDate BETWEEN '2020-11-01 00:00:00.000' AND '2020-11-30 23:59:59.997' 
) y 
WHERE y.AgentId = 3 AND y.QuoteId IS NOT NULL 
ORDER BY y.QuoteId asc, y.BridgedDate asc;

2. 使用value()方法的修正版 #

同样需要先声明命名空间，再调整XPath路径：

WITH XMLNAMESPACES(DEFAULT 'http://www.ACORD.org/standards/PC_Surety/ACORD1.11.0/xml/')
SELECT rtr.BridgedDate, 
       rtr.XMLRequest, 
       rtr.XMLRequest.value('(/ACORD/SignonRq/ClientApp/Org)[1]','varchar(max)') as RaterValue 
FROM dbo.AUT_RealTimeRequest rtr 
WHERE rtr.QuoteId IS NOT NULL 
  AND rtr.AgentId = 3 
  AND rtr.BridgedDate BETWEEN '2020-11-01 00:00:00.000' AND '2020-11-30 23:59:59.997' 
ORDER BY rtr.QuoteId asc, rtr.BridgedDate asc;

简单说，只要在查询开头加上WITH XMLNAMESPACES声明对应的命名空间，SQL Server就能正确识别XML里的元素，顺利提取出<Org>的值啦！

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