Extracting XML (as xml type) using xpath query in SQL

Question

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 work in a SQL query:

Table.value('(/head/instance/tag1)[1]', 'varchar(max)') as "col"

Any help would be great.

Thanks

@user270370: This is not asking for an XPath expression but for no standar SQL type conversion — user357812
– user357812, Commented Jan 13, 2011 at 14:13

user492238 · Accepted Answer · 2011-01-13 14:08:21Z

2

this should work:

Select Cast(Table.xmlcolumnname.query('/head/instance/tag1') as varchar(max)) 'col';

(its not checked! may contain typo..)

answered Jan 13, 2011 at 14:08

user492238

4,0841 gold badge24 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extracting XML (as xml type) using xpath query in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related