Reputation: 611
I have two tables with structure similar to this:
Table1 ([Table1Id] INT,
[Sequence] INT,
[Table2Id] INT)
Table2 ([Table2Id] INT,
[XMLData] XML)
The essential part of the XML in table 2 is:
<Root>
<Record>
<Sequence>1</Sequence>
<Order>
<Product>Widget</Product>
</Order>
</Record>
<Record>
<Sequence>2</Sequence>
<Order>
<Product>Bolt</Product>
</Order>
</Record>
</Root>
I need to be able to join to table 2 from table 1 then extract the appropriate Record based on the sequence number so as to get a result set like so:
Table1Id Table2Id Sequence Product
-------- -------- -------- -----------
1 1 1 Widget
1 1 2 Bolt
I'm not sure how to go about getting this result. Any help would be greatly appreciated.
Upvotes: 2
Views: 404
Reputation: 139010
Use sql:column()
in a predicate in the xquery where you compare the value of Sequence in the XML with the value of Sequence in Table1.
select T1.Table1ID,
T1.Table2Id,
T1.Sequence,
T2.XMLData.value('(/Root/Record[Sequence/text() = sql:column("T1.Sequence")]/Order/Product/text())[1]', 'nvarchar(50)') as Product
from Table1 as T1
inner join Table2 as T2
on T1.Table2Id = T2.Table2Id
Upvotes: 2