Category: General SQL SQL Server

If you are querying the XML datatype you can query nodes using the sql below:

select 

cast(DOC as xml).query('//trade_id/text()') as trade_id, 
cast(DOC as xml).query('//cdr/text()') as cdr, 
cast(DOC as xml).query('//type_instrument/text()') as 'type_instrument', 
cast(DOC as xml).query('(//product/@type)[0') as '@type'


from Deals

You may receive this error if you try to query an attribute:

Attribute may not appear outside of an element

Use the data() function to query attributes and resolve this problem

cast(DOC as xml).query('data(//product/@type)') as '@type'

 

Tags:

SQL server

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.