Xpath query on an XML with namespace
-
Hey folks,
I am working on a project where an external system drops off some XMLs into a table,
create table dirbts
(
id int identity,
txnid nvarchar(50),
contents xml
);
Below is a sample XML in the contents field of the table
(forgive my formatting)
[data
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://com.btsws.schema.bc.order"
SequenceNumber="1"
TransactionId="d45909ed-2ca7-dc11-a41a-00b0d068318a"]
[main]
[row]
[order_id]SV0711270001@@1[/order_id]
[/row]
[/main]
[/data]
I need to pull the order_id element; SequenceNumber and TransactionId attributes. I am running the following query to fetch data. Its gets order_id element but not the attributes (get empties or null), any thoughts on what I am doing wrong
WITH XMLNAMESPACES( 'http://com.btsws.schema.bc.order' AS "s")
select
cast(
contents.query
('/s:data/s:main/s:row/s:order_id/text()') as nvarchar(1000)),
contents.value
('/s:data[@s:SequenceNumber][1]','int')
from dirbts
order by id
I have tried query function, without namespace etc. to no avail.
thanks in advance.
-
WITH XMLNAMESPACES( 'http://com.btsws.schema.bc.order' AS "s")select
cast(
contents.query('/s:data/s:main/s:row/s:order_id/text()') as nvarchar(1000)),
contents.value('(/s:data)[1]/@SequenceNumber','int')
from dirbts
order by id
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
awesome, that works. thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply