T-SQL parsing XML giving blank value

Question

I have been through the numerous questions on this issue here and none of those resolutions seem to work. I have the following XML:

DECLARE @XML xml;
SELECT @XML ='<?xml version="1.0" encoding="UTF-8"?>
<hb:MedicalAidMessage xmlns:hb="bridge.co.za/mes" Version="6.0.0">
    <Claim>
        <Details>
            <LineItems>
                <LineItem>
                    <HBBatchNum>2414</HBBatchNum>
                </LineItem>
            </LineItems>
        </Details>
    </Claim>
</hb:MedicalAidMessage>';

and this code to parse it:

;WITH XMLNAMESPACES ('bridge.co.za/mes' as ns)
SELECT
    ISNULL(T.N.value('HBBatchNum[1]', 'INT'), 0) AS BatchNo
FROM
    @XML.nodes('/Claim/Details/LineItems/LineItem[1]') AS T(N)

Which is returning a blank value instead of 2414. In the live query the XML is stored in a table column. I just cannot figure out why I am not getting the node value.

Salman A · Accepted Answer · 2019-04-15T10:04:49.590

4

It is fairly obvious... Claim is not the root element. So use one of the following:

WITH XMLNAMESPACES ('bridge.co.za/mes' AS ns)
SELECT ISNULL(T.N.value('HBBatchNum[1]', 'int'),0) AS BatchNo
FROM @XML.nodes('/ns:MedicalAidMessage/Claim/Details/LineItems/LineItem[1]') AS T(N)

-- WITH XMLNAMESPACES ('bridge.co.za/mes' AS ns)
SELECT ISNULL(T.N.value('HBBatchNum[1]', 'int'),0) AS BatchNo
FROM @XML.nodes('/*:MedicalAidMessage/Claim/Details/LineItems/LineItem[1]') AS T(N)

edited Apr 15 '19 at 10:04

answered Apr 15 '19 at 09:45

Salman A

262,204
82
430
521

1

No namespace registration necessary when you use `/*/Claim/Details/LineItems/LineItem[1]`. – Tomalak Apr 15 '19 at 09:48
@Tomalak, using `*` when it's not absolutely necessary is a very bad idea, performance wise. – Roger Wolf Apr 15 '19 at 10:15
1

@RogerWolf No, it isn't, really. And it's definitely not in this case. – Tomalak Apr 15 '19 at 10:17
When running this in the live DDB I get the following error: Msg 9402, Level 16, State 1, Line 1 XML parsing: line 1, character 38, unable to switch the encoding – Marc L Apr 15 '19 at 10:36
@marc perhaps your data is not stored as XML. See this question: https://stackoverflow.com/questions/44892059/msg-9402-level-16-state-1-line-9-xml-parsing-line-1-character-38-unable-to – Salman A Apr 15 '19 at 10:42
You're right- whomever designed the dtaabase put the XML message into a varchar column... Ah well, guess I'll just have to parse it as a string... – Marc L Apr 15 '19 at 10:46

T-SQL parsing XML giving blank value

1 Answers1