MS SQL Server解析XML返回空结果，如何获取目标内容？

你的代码存在几个关键问题，导致无法正确解析XML数据，以下是修正方案：

错误分析 #

• 错误修改XML结构：你用replace去掉了XML中的冒号，破坏了命名空间前缀hb:的结构，而XML的命名空间声明xmlns:hb="address.co.za/messaging"依然存在，导致节点识别失败。
• 路径拼写错误：XQuery路径中的Reponses是拼写错误，正确节点名是Responses。
• 未处理XML命名空间：XML带有命名空间前缀，但查询未声明和使用该命名空间，无法定位到根节点。

修正后的代码 #

declare @tempxml as table (xmlstr varchar(max));
insert into @tempxml
values ('<?xml version="1.0" encoding="UTF-8"?>
<hb:MedicalAidMessage xmlns:hb="address.co.za/messaging"
                      Version="6.0.0">
    <Claim>
        <Details>
            <Responses>
                <Response Type="Error">
                    <Code>6</Code>
                    <Desc>Your claim has been rejected on 2022/10/22. Your claim has been rejected. Reason: This is an inactive scheme. Please contact the Client Service Centre on 123456789 or at email@mail.com for assistance.</Desc>
                </Response>
            </Responses>
        </Details>
    </Claim>
</hb:MedicalAidMessage> ')

declare @XMLData xml
set @XMLData = (select xmlstr from @tempxml)

-- 声明XML命名空间
WITH XMLNAMESPACES ('address.co.za/messaging' AS hb)
select [Reason] = n.value('Desc[1]', 'nvarchar(2000)')
  from @XMLData.nodes('/hb:MedicalAidMessage/Claim/Details/Responses/Response') as a(n)

修改说明 #

1. 移除错误的replace操作：保留XML原始结构，包括命名空间前缀hb:和命名空间声明。
2. 添加命名空间声明：通过WITH XMLNAMESPACES指定XML中使用的命名空间，让XQuery能识别带hb:前缀的根节点。
3. 修正路径拼写：将Reponses改为正确的Responses。
4. 明确XML赋值逻辑：原代码select *改为select xmlstr，避免因表结构变化导致的潜在问题。

执行修正后的代码，即可得到你预期的结果。

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