CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlxpath
Marc L
Marc L

Reputation: 877

T-SQL parsing XML giving blank value

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.

Upvotes: 1

Views: 116

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272006

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)

Upvotes: 4

Related Questions