June 11, 2019 at 8:03 pm
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/
June 11, 2019 at 9:28 pm
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?
June 11, 2019 at 9:30 pm
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?
June 12, 2019 at 5:58 pm
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/
June 12, 2019 at 8:00 pm
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply