question

rjford11 avatar image
rjford11 asked

Parse muti-rooted XML with Namespaces in SQL Server

Have XML stored in database and trying to get specific attributes.

There are 2 roots in the XML as well as namespaces.

Any ideas on how best to grab attributes?

This is snippet of the XML:

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">

</s:Schema>
<rs:data>
<z:row GmID="550733" ReadingTime="2019-09-23T15:44:58" DataSequenceNumber="132978"/>

</rs:data>

xml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

I'm not sure which attributes you wanted, but this snippet shows how to extract all the attributes from that XML

declare @YourTable table (somexml xml)

insert into @YourTable (somexml)
select '<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
</s:Schema>
<rs:data>
<z:row GmID="550733" ReadingTime="2019-09-23T15:44:58" DataSequenceNumber="132978"/>
</rs:data>
</xml>';


with xmlnamespaces ('uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' as s,
		'uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' as dt,
		'urn:schemas-microsoft-com:rowset' as rs,
		'#RowsetSchema' as z
		)
select
 somexml.value('(/xml/s:Schema/@id)[1]','varchar(max)') as id,
 somexml.value('(/xml/rs:data/z:row/@GmID)[1]','varchar(max)') as GmID,
 somexml.value('(/xml/rs:data/z:row/@ReadingTime)[1]','varchar(max)') as ReadingTime,
 somexml.value('(/xml/rs:data/z:row/@DataSequenceNumber)[1]','varchar(max)') as DataSequenceNumber

from @YourTable

returns

id             GmID      ReadingTime         DataSequenceNumber
-------------- --------- ------------------- -------------------
RowsetSchema   550733    2019-09-23T15:44:58 132978

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.