I have a table with an xml column which contains the below data.
<a:Tracking>
<b:TrackingID>343545</b:TrackingID>
<b:TrackingID n:id="abc">12345</b:TrackingID>
</a:Tracking>
I need to get the value of the node b:TrackingID where it contains an attribute n:id with a value "abc".
I am trying the below code without any success. Does anyone have any ideas? Thank you!
WITH XMLNAMESPACES ('http://nm.gov/nm/2.0' as b,
'http://nm.gov/nm/structures/2.0' as n)
Select @TrackingID = Message.value('(//b:TrackingID)[@n:id = "abc"]', 'varchar(100)')
From tblData e WITH(NOLOCK)
Where ID = 12