CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
Lance
Lance

Reputation: 611

Join an XML Node in a column to a field in another table

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

Answers (1)

Mikael Eriksson
Mikael Eriksson

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

SQL Fiddle

Upvotes: 2

Related Questions