CODEWITHSUNDEEP
sql-servert-sqlxpathxml-namespacesxquery-sql
Barış ERDOĞAN
Barış ERDOĞAN

Reputation: 109

How to parse multiple namespaces xml in sql query

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'

Upvotes: 1

Views: 452

Answers (1)

Thom A
Thom A

Reputation: 95561

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

Upvotes: 1

Related Questions