在MS SQL SERVER中解析xml时得到空白结果

Question

我试图解析存储在SQL server表中的XML数据。我尝试使用以下代码(调整以删除个人信息并显示设置)，但它返回一个空白。有什么方法可以做到这一点，以获得我的结果?预期的结果应该是

您的索赔已于2022/10/22被拒绝。你的索赔已经拒绝。原因:这是一个不活跃的方案。请与客户服务中心电话:123456789或电邮:[e-mail]查询援助。

declare @tempxml as table (xmlstr varchar(max));
insert into @tempxml
values (replace('<?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 [e-mail] for assistance.</Desc>
</Response>
</Responses>
</Details>
</Claim>
</hb:MedicalAidMessage> ',':',''))
declare @XMLData xml
set @XMLData = (select * from @tempxml)
select [Reason] = n.value('Desc[1]', 'nvarchar(2000)')
from @XMLData.nodes('/hbMedicalAidMessage/Claim/Details/Reponses/Response') as a(n)

感谢

Answer 1

考虑以下查询，它们演示了正确访问名称空间引用元素的两种方法:

select [Reason] = Response.value('(Desc/text())[1]', 'nvarchar(2000)')
from @XMLData.nodes('
declare namespace foo = "address.co.za/messaging";
/foo:MedicalAidMessage/Claim/Details/Responses/Response') as a(Response);

with xmlnamespaces('address.co.za/messaging' as foo)
select [Reason] = Response.value('(Desc/text())[1]', 'nvarchar(2000)')
from @XMLData.nodes('/foo:MedicalAidMessage/Claim/Details/Responses/Response') as a(Response);

请注意，查询中的名称空间前缀foo与原始XML中的hb前缀不匹配。不是前缀需要匹配XML，而是它们引用的名称空间，在所有情况下，都是address.co.za/messaging。