SQL Server解析带命名空间的XML读取节点值返回null如何解决

你已经通过nodes()方法将查询上下文定位到了del:Header节点下，此时返回的h对应的就是单个del:Header节点，你在value()方法中写的绝对路径/del:MessageId是从XML根节点开始查找的，根节点下不存在直接的del:MessageId节点，因此返回null。

写法1：保留现有nodes()定位，使用相对路径取值 #

最常用的写法，适合需要批量解析同层级多个子字段的场景：

WITH XMLNAMESPACES(N'http://schemas.fortum.com/amm/delivermeterreading' AS del)
SELECT
    t.file_name, 
    t.file_created_time received_timestamp,
    h.value(N'(del:MessageId)[1]','varchar(40)') AS MessageId,
    h.value(N'(del:MessageType)[1]','varchar(20)') AS MessageType,
    h.value(N'(del:MessageCreatedTimestamp)[1]','datetimeoffset') AS MessageCreatedTimestamp
FROM
    load.t t
OUTER APPLY
    t.xml_data.nodes('/del:DeliverMeterReading/del:Header') AS m(h)

写法2：不拆分节点，直接从根路径取值 #

适合Header仅存在1个、不需要拆分行的场景：

WITH XMLNAMESPACES(N'http://schemas.fortum.com/amm/delivermeterreading' AS del)
SELECT
    t.file_name, 
    t.file_created_time received_timestamp,
    t.xml_data.value(N'(/del:DeliverMeterReading/del:Header/del:MessageId)[1]','varchar(40)') AS MessageId
FROM load.t t

如果需要解析下层Data节点和读数属性，可以按以下写法实现一行对应一条读数记录：

WITH XMLNAMESPACES(N'http://schemas.fortum.com/amm/delivermeterreading' AS del)
SELECT
    t.file_name, 
    t.file_created_time received_timestamp,
    -- Header公共字段
    m.h.value(N'(del:MessageId)[1]','varchar(40)') AS MessageId,
    -- Data业务字段
    d.data.value(N'(del:DeliverySiteEANCode)[1]','varchar(20)') AS DeliverySiteEANCode,
    d.data.value(N'(del:ReadingStartDate)[1]','datetimeoffset') AS ReadingStartDate,
    -- Value读数属性
    v.val.value(N'@position','int') AS Position,
    v.val.value(N'@reading','decimal(18,2)') AS ReadingValue,
    v.val.value(N'@readingDate','datetimeoffset') AS ReadingDate,
    v.val.value(N'@status','int') AS ReadingStatus
FROM
    load.t t
OUTER APPLY t.xml_data.nodes('/del:DeliverMeterReading/del:Header') AS m(h)
OUTER APPLY t.xml_data.nodes('/del:DeliverMeterReading/del:DataRows/del:Data') AS d(data)
OUTER APPLY d.data.nodes('del:Values/del:Value') AS v(val)

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