XML column in table with join SQL Server

Question

I've got a table with an xml column called data (xml has got id node) and second table with idSecond column.

I would like to perform join between these two tables so that data[id] = secondTable.ID

What would be the syntax for this in SQL Server 2005 ?

thanks for any hints

Remus Rusanu · Accepted Answer · 2011-12-13 20:15:39Z

3

Use xml.value() method to project the node id, then join on the projected value. Eg:

with x as (
select xmlcolumn.value(N'xquery_for_node...', N'type') as id,
   ...
from xmltable)
select ...
from x join secondtable on x.id = ...

Actual syntax and xquery used depends on the schema of your tables and the XML content. If there are multiple node ids to project then use xml.nodes() in a cross apply instead.

answered Dec 13, 2011 at 20:15

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

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

Collectives™ on Stack Overflow

XML column in table with join SQL Server

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