Extracting XML (as xml type) using xpath query in SQL
I'm trying to extract a chunk of XML (ie the whole xml of a node, not just content) using an xpath query in SQL. I can extract a single content field but am not sure how to do the above.
Say the xml is as follows
<head> <instance> <tag1> <tag2>data</tag2> <tag3>data</tag3> </tag1> </instance> </head>
I would like to extract all of the xml inside tag1, and was hoping something like this would w开发者_如何学运维ork in a SQL query:
Table.value('(/head/instance/tag1)[1]', 'varchar(max)') as "col"
Any help would be great.
Thanks
this should work:
Select Cast(Table.xmlcolumnname.query('/head/instance/tag1') as varchar(max)) 'col';
(its not checked! may contain typo..)
精彩评论