Getting blank result when parsing xml in MS SQL SERVER

Question

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

You haven't defined your namespaces. The first node isn't hbMedicalAidMessage, it's MedicalAidMessage in the namespace hb. — Thom A
– Thom A ♦, Commented Nov 9, 2022 at 9:06
Hmm, the example I copied this from did not define a namespace and seemed to work. how do I define a namespace? — Marc L
– Marc L, Commented Nov 9, 2022 at 9:08
Not following how that should look. What would be an example of that declaration? — Marc L
– Marc L, Commented Nov 9, 2022 at 9:11
Does this answer your question? Select in XML with namespace returning null — Thom A
– Thom A ♦, Commented Nov 9, 2022 at 9:12

AlwaysLearning · Accepted Answer · 2022-11-09 09:32:14Z

1

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.

answered Nov 9, 2022 at 9:32

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting blank result when parsing xml in MS SQL SERVER

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related