XML with Namespaces

  • Mike01

    SSChampion

    Points: 11070

    I have been kicking this around for a little while and am stuck.  I have an xml variable with 4 namespaces in it. I am trying to get ChainNumber, but I can't figure it out.  When I select MerchandiseMasterDivision, it shows all 4 attributes under it in 1 column.

     

    Thanks in advance.  SQL is below

     

    declare @x xml = '<ns5:SyncMerchandiseHierarchyRequest 
    xmlns:ns3="http://www.xmlns.DummyValue.com/common/datatypes/MessageHeader/1.0"
    xmlns:ns4="http://www.xmlns.DummyValue.com/canonical/masterdata/MerchandiseHierarchy/2.0"
    xmlns:ns2="http://www.xmlns.DummyValue.com/common/datatypes/Verb/1.0"
    xmlns:ns5="http://www.xmlns.DummyValue.com/service/envelope/MerchandiseHierarchy/2.0">
    <ns3:DummyValueMessageHeader>
    <ns3:SubscriberID>1395076682J</ns3:SubscriberID>
    <ns3:ConsumerID>1432228473.721-SyncMerchHierSvc-2.0</ns3:ConsumerID>
    <ns3:ServiceName>SyncMerchandiseHierarchyService</ns3:ServiceName>
    <ns3:OperationName>SyncMerchandiseHierarchy</ns3:OperationName>
    <ns3:ServiceVersion>2</ns3:ServiceVersion>
    <ns3:AppID>HIERARCHYMANAGER</ns3:AppID>
    <ns3:TransactionID>5_98</ns3:TransactionID>
    <ns3:TransactionType>MerchandiseMasterDivision</ns3:TransactionType>
    <ns3:CreationDateTime>2017-08-07T16:28:48</ns3:CreationDateTime>
    <ns3:ReferenceID>1</ns3:ReferenceID>
    <ns3:ConfirmationCode>CODO</ns3:ConfirmationCode>
    <ns3:UserCredentials>
    <ns3:UserName>tonyf</ns3:UserName>
    <ns3:Password>tonyf</ns3:Password>
    </ns3:UserCredentials>
    </ns3:DummyValueMessageHeader>
    <ns5:SyncMerchandiseHierarchy>
    <ns2:Sync actionCode="Create"/>
    <ns4:MerchandiseHierarchy>
    <ns4:MerchandiseMasterDivision>
    <ChainNumber>05</ChainNumber>
    <MasterDivisionNumber>98</MasterDivisionNumber>
    <Name>98 BTK TEST MDIV</Name>
    <UserID>sam00321</UserID>
    <OldNumber>98</OldNumber>
    </ns4:MerchandiseMasterDivision>
    </ns4:MerchandiseHierarchy>
    </ns5:SyncMerchandiseHierarchy>
    </ns5:SyncMerchandiseHierarchyRequest>'


    ;with XMLNAMESPACES('http://www.xmlns.DummyValue.com/common/datatypes/Verb/1.0' as ns2,
    'http://www.xmlns.DummyValue.com/common/datatypes/MessageHeader/1.0' as ns3,
    'http://www.xmlns.DummyValue.com/canonical/masterdata/MerchandiseHierarchy/2.0' as ns4,
    DEFAULT 'http://www.xmlns.DummyValue.com/service/envelope/MerchandiseHierarchy/2.0')


    select
    fields.value('(../ns3:DummyValueMessageHeader/ns3:TransactionType)[1]', 'varchar(100)') AS TransactionType
    ,fields.value('(../ns3:DummyValueMessageHeader/ns3:CreationDateTime)[1]', 'datetime2(7)') AS CreatedDate
    ,fields.value('(ns2:Sync/@actionCode)[1]', 'varchar(100)') AS actionCode
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:MerchandiseMasterDivision/ChainNumber)[1]', 'varchar(100)') AS ChainNumber
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:MerchandiseMasterDivision)[1]', 'varchar(100)') AS MerchandiseMasterDivision
    from @x.nodes('//SyncMerchandiseHierarchyRequest/SyncMerchandiseHierarchy') as xmldata(fields)

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Matt Miller (4)

    SSC Guru

    Points: 124173

    This is a "quick and dirty" trick, but simply add *: in front of ChainNumber in your query.  Of course that will work assuming no 2 of your namespaces have an Element called ChainNumber.

     

     

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt Miller (4)

    SSC Guru

    Points: 124173

    As in :

     

    select 
    fields.value('(../ns3:DummyValueMessageHeader/ns3:TransactionType)[1]', 'varchar(100)') AS TransactionType
    ,fields.value('(../ns3:DummyValueMessageHeader/ns3:CreationDateTime)[1]', 'datetime2(7)') AS CreatedDate
    ,fields.value('(ns2:Sync/@actionCode)[1]', 'varchar(100)') AS actionCode
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:MerchandiseMasterDivision/*:ChainNumber)[1]', 'varchar(100)') AS ChainNumber
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:MerchandiseMasterDivision)[1]', 'varchar(100)') AS MerchandiseMasterDivision
    from @x.nodes('//SyncMerchandiseHierarchyRequest/SyncMerchandiseHierarchy') as xmldata(fields)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Mike01

    SSChampion

    Points: 11070

    Thanks Matt, but the * doesn't work in some occasions.  I have multiple VatRates I'm trying to get and it only returns 1.  This should return all 6 Vat records

     

    Set transaction isolation level read uncommitted
    go
    declare @x xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <ns5:SyncMerchandiseHierarchyRequest xmlns:ns5="http://www.xmlns.DummyValue.com/service/envelope/MerchandiseHierarchy/2.0" xmlns:ns2="http://www.xmlns.DummyValue.com/common/datatypes/Verb/1.0" xmlns:ns4="http://www.xmlns.DummyValue.com/canonical/masterdata/MerchandiseHierarchy/2.0" xmlns:ns3="http://www.xmlns.DummyValue.com/common/datatypes/MessageHeader/1.0">
    <ns3:DummyValueMessageHeader>
    <ns3:SubscriberID>1395076682J</ns3:SubscriberID>
    <ns3:ConsumerID>1432228473.721-SyncMerchHierSvc-2.0</ns3:ConsumerID>
    <ns3:ServiceName>SyncMerchandiseHierarchyService</ns3:ServiceName>
    <ns3:OperationName>SyncMerchandiseHierarchy</ns3:OperationName>
    <ns3:ServiceVersion>2</ns3:ServiceVersion>
    <ns3:AppID>HIERARCHYMANAGER</ns3:AppID>
    <ns3:TransactionID>5_98_98</ns3:TransactionID>
    <ns3:TransactionType>Class</ns3:TransactionType>
    <ns3:CreationDateTime>2017-08-08T11:27:34</ns3:CreationDateTime>
    <ns3:ReferenceID>1</ns3:ReferenceID>
    <ns3:ConfirmationCode>CODO</ns3:ConfirmationCode>
    <ns3:UserCredentials>
    <ns3:UserName>tonyf</ns3:UserName>
    <ns3:Password>tonyf</ns3:Password>
    </ns3:UserCredentials>
    </ns3:DummyValueMessageHeader>
    <ns5:SyncMerchandiseHierarchy>
    <ns2:Sync actionCode="Create"/>
    <ns4:MerchandiseHierarchy>
    <ns4:Class>
    <ChainNumber>05</ChainNumber>
    <MasterDivisionNumber>98</MasterDivisionNumber>
    <DivisionNumber>98</DivisionNumber>
    <MasterDepartmentNumber>98</MasterDepartmentNumber>
    <DepartmentNumber>98</DepartmentNumber>
    <ClassNumber>98</ClassNumber>
    <Name>98 BTK CLASS</Name>
    <UserID>sam00321</UserID>
    <VATChangeFlag>Y</VATChangeFlag>
    <ns4:ClassData>
    <Seasonality>B</Seasonality>
    <AvailableForUse>Y</AvailableForUse>
    </ns4:ClassData>
    <ns4:VATRate>
    <Country>GB</Country>
    <Regime>GB VAT</Regime>
    <RegionNbr>1000</RegionNbr>
    GB VAT STD
    <RateId>100001</RateId>
    <Rate>20.00</Rate>
    </ns4:VATRate>
    <ns4:VATRate>
    <Country>AT</Country>
    <Regime>AT VAT</Regime>
    <RegionNbr>4000</RegionNbr>
    AT VAT STD
    <RateId>400001</RateId>
    <Rate>20.00</Rate>
    </ns4:VATRate>
    <ns4:VATRate>
    <Country>DE</Country>
    <Regime>DE VAT</Regime>
    <RegionNbr>5000</RegionNbr>
    DE VAT STD
    <RateId>500001</RateId>
    <Rate>19.00</Rate>
    </ns4:VATRate>
    <ns4:VATRate>
    <Country>IE</Country>
    <Regime>IE VAT</Regime>
    <RegionNbr>3000</RegionNbr>
    IE VAT STD
    <RateId>300001</RateId>
    <Rate>23.00</Rate>
    </ns4:VATRate>
    <ns4:VATRate>
    <Country>NL</Country>
    <Regime>NL VAT</Regime>
    <RegionNbr>6000</RegionNbr>
    NL VAT STD
    <RateId>600001</RateId>
    <Rate>21.00</Rate>
    </ns4:VATRate>
    <ns4:VATRate>
    <Country>PL</Country>
    <Regime>PL VAT</Regime>
    <RegionNbr>2000</RegionNbr>
    PL VAT STD
    <RateId>200001</RateId>
    <Rate>23.00</Rate>
    </ns4:VATRate>
    </ns4:Class>
    </ns4:MerchandiseHierarchy>
    </ns5:SyncMerchandiseHierarchy>
    </ns5:SyncMerchandiseHierarchyRequest>'

    ;with XMLNAMESPACES('http://www.xmlns.DummyValue.com/common/datatypes/Verb/1.0' as ns2,
    'http://www.xmlns.DummyValue.com/common/datatypes/MessageHeader/1.0' as ns3,
    'http://www.xmlns.DummyValue.com/canonical/masterdata/MerchandiseHierarchy/2.0' as ns4,
    DEFAULT 'http://www.xmlns.DummyValue.com/service/envelope/MerchandiseHierarchy/2.0')

    select
    fields.value('(../ns3:DummyValueMessageHeader/ns3:TransactionType)[1]', 'varchar(100)') AS TransactionType
    ,fields.value('(../ns3:DummyValueMessageHeader/ns3:CreationDateTime)[1]', 'datetime2(7)') AS CreatedDate
    ,fields.value('(ns2:Sync/@actionCode)[1]', 'varchar(100)') AS actionCode
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/*:ChainNumber)[1]', 'varchar(100)') AS ChainNumber
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/*:MasterDivisionNumber)[1]', 'int') AS MerchandiseMasterDivision
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/*:DivisionNumber)[1]', 'int') AS DivisionNumber
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/*:MasterDepartmentNumber)[1]', 'int') AS MasterDepartmentNumber
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/*:DepartmentNumber)[1]', 'int') AS DepartmentNumber
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/*:ClassNumber)[1]', 'int') AS ClassmentNumber
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/ns4:VATRate/*:Country)[1]', 'varchar(4)') AS Country
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/ns4:VATRate/*:Regime)[1]', 'varchar(20)') AS Regime
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/ns4:VATRate/*:RegionNbr)[1]', 'int') AS RegionNbr
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/ns4:VATRate/*:Code)[1]', 'varchar(50)') AS Code
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/ns4:VATRate/*:RateId)[1]', 'int') AS RateId
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/ns4:VATRate/*:Rate)[1]', 'decimal(12,4)') AS Rate
    from @x.nodes('//SyncMerchandiseHierarchyRequest/SyncMerchandiseHierarchy') as xmldata(fields)

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Matt Miller (4)

    SSC Guru

    Points: 124173

    That's because you essentially have a one to many link between the class and vat rates level, and you're only returning the first one.  That's what the [1] is doing in the xml.value syntax.  The best way to return multiple is to break that sub-section into a secon table and use cross apply (kind of acts like a JOIN would in SQL).

     

    select 
    fields.value('(../ns3:DummyValueMessageHeader/ns3:TransactionType)[1]', 'varchar(100)') AS TransactionType
    ,fields.value('(../ns3:DummyValueMessageHeader/ns3:CreationDateTime)[1]', 'datetime2(7)') AS CreatedDate
    ,fields.value('(ns2:Sync/@actionCode)[1]', 'varchar(100)') AS actionCode
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/*:ChainNumber)[1]', 'varchar(100)') AS ChainNumber
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/*:MasterDivisionNumber)[1]', 'int') AS MerchandiseMasterDivision
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/*:DivisionNumber)[1]', 'int') AS DivisionNumber
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/*:MasterDepartmentNumber)[1]', 'int') AS MasterDepartmentNumber
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/*:DepartmentNumber)[1]', 'int') AS DepartmentNumber
    ,fields.value('(ns4:MerchandiseHierarchy/ns4:Class/*:ClassNumber)[1]', 'int') AS ClassmentNumber
    ,subfields.value('(*:Country)[1]', 'varchar(4)') AS Country
    ,subfields.value('(*:Regime)[1]', 'varchar(20)') AS Regime
    ,subfields.value('(*:RegionNbr)[1]', 'int') AS RegionNbr
    ,subfields.value('(*:Code)[1]', 'varchar(50)') AS Code
    ,subfields.value('(*:RateId)[1]', 'int') AS RateId
    ,subfields.value('(*:Rate)[1]', 'decimal(12,4)') AS Rate
    from @x.nodes('//SyncMerchandiseHierarchyRequest/SyncMerchandiseHierarchy') as xmldata(fields)
    cross apply fields.nodes('ns4:MerchandiseHierarchy/ns4:Class/ns4:VATRate') as fields(subfields)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply