Getting blank result when parsing xml in MS SQL SERVER-CodePudding

I am trying to parse XML data stored in a SQL server table. I have tried using the following code (adjusted to remove personal information and to show the setup) but it is returning a blank. Is there some way to do this to get my result? The expected result should be

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 protected] for assistance.

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 [email protected] 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)

Thanks

CodePudding user response：

Consider the following queries which demonstrate two ways to access the namespace-referenced elements correctly:

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);

Note that the namespace prefix foo in the queries does not match the hb prefix in the original XML. It's not the prefixes that need to match the XML but the namespaces they reference which, in all cases, is address.co.za/messaging.