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