How to parse multiple namespaces xml in sql query

Question

I have a table which includes an XML column and I'm trying to fetch certain columns from the XML data. Those columns are under the PositionRoles tag which is part of an has INSERT statement. Unfortunately, I'm getting NULL values.

Here is the XML example I used:

<request-broker-message version="1.0">
    <request class="UPDATE_ORGANISATION_HIERARCHY" culture="tr-TR">
        <parameter name="OrgHierarchyDatasets_schema">
            (here is not important...)
        </parameter>
        <parameter name="OrgHierarchyDatasets_diffgram">
            <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
                <NewDataSet>
                    <PositionRoles diffgr:id="PositionRoles1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
                        <POSTN_ID>0000-0000H1-POS</POSTN_ID>
                        <ROLE_ID>0000-00002B-ROL</ROLE_ID>
                        <STATUS>1</STATUS>
                    </PositionRoles>
                </NewDataSet>
            </diffgr:diffgram>
        </parameter>
        <parameter name="ExistNodeCheck" type="System.Boolean">True</parameter>
    </request>
</request-broker-message>

And my attempt:

;WITH XMLNAMESPACES('urn:schemas-microsoft-com:xml-diffgram-v1' AS diffgr,
                    'inserted' AS hasChanges)

INSERT INTO TMP_ORGANIZATION_HIERARCHY_LOGS_TABLE(POSITION_ID, OPERATION, ROLE_ID, PACKET_OWNER, RESPONSE_TIME)
SELECT 
* 
FROM TMP_PACKET_LOG_TABLE (NOLOCK)
WHERE 
RESPONSE_PACKET.value('(/request-broker-message/request/parameter[@name="OrgHierarchyDatasets_diffgram"]//NewDataSet/hasChanges:PositionRoles/hasChanges:STATUS)[1]', 'varchar(1)') = '1'

Thom A · Accepted Answer · 2019-12-30T14:13:18.413

Not sure what specific values you're after, but this should be more than enough to get you there. You need to put the appropriate namespaces in the WITH (which doesn't start with an ;, the statement ends with one), and then prefix the relevant nodes with the relevant namespace name (for example diffgr: for diffgram):

DECLARE @XML xml =
'<request-broker-message version="1.0">
    <request class="UPDATE_ORGANISATION_HIERARCHY" culture="tr-TR">
        <parameter name="OrgHierarchyDatasets_schema">
            (here is not important...)
        </parameter>
        <parameter name="OrgHierarchyDatasets_diffgram">
            <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
                <NewDataSet>
                    <PositionRoles diffgr:id="PositionRoles1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
                        <POSTN_ID>0000-0000H1-POS</POSTN_ID>
                        <ROLE_ID>0000-00002B-ROL</ROLE_ID>
                        <STATUS>1</STATUS>
                    </PositionRoles>
                </NewDataSet>
            </diffgr:diffgram>
        </parameter>
        <parameter name="ExistNodeCheck" type="System.Boolean">True</parameter>
    </request>
</request-broker-message>';

WITH XMLNAMESPACES ('urn:schemas-microsoft-com:xml-diffgram-v1' AS diffgr,
                    'urn:schemas-microsoft-com:xml-msdata' AS msdata)
SELECT dg.NDS.value('(PositionRoles/@diffgr:id)[1]','varchar(30)') AS id,
       dg.NDS.value('(PositionRoles/@msdata:rowOrder)[1]','int') AS rowOrder,
       dg.NDS.value('(PositionRoles/POSTN_ID/text())[1]','varchar(30)') AS POSTN_ID
FROM (VALUES(@XML))V(X)
     CROSS APPLY V.X.nodes('request-broker-message/request/parameter/diffgr:diffgram/NewDataSet') dg(NDS);

I've also remove the NOLOCK hit, as I suspect it's being misused: Bad habits : Putting NOLOCK everywhere

Thanks for attention, I used directly query like ...PositionRoles[@diffgr:hasChanges="inserted"]/STATUS/text()) . And it solved problem urgently. However, then I will try later CROSS APPLY method then. — Barış ERDOĞAN, Jan 02 '20 at 07:36

How to parse multiple namespaces xml in sql query

1 Answers1