Join Table on xml column.

Question

I have 2 tables as follows on Sql Server 2005 database

Request(RequestId int, Filter xml)
DataTable(Id int,.....)

The filter column has a list of ids from datatable as xml for e.g. 1013

Now I want to select the data from DataTable that match the ids in the filter xml. Here is what I have come up with

select d.*
   from request 
   cross apply filter.nodes('Ids/Id') as Ids(id)
   inner join DataTable d on d.id = Ids.Id.value('.', 'int')
   where requestid = 35

This works but I want to know if this is the best way to do this.

Answer 1

This is how I do similar joins (from xml parameter onto tables)... if that helps.

It's clean and understandable

Answer 2

This is the only way ive been able to pull off joins from multiple rows of a table with an xml column. one other method ive used is combining the entire resultset into a single XML column using a CTE and using a cross apply from the CTE. The example above would be:

WITH tmpCTE AS
(
    SELECT
        (
            SELECT
                r.RequestID AS [requestid]
            ,   r.Filter.query('//id') AS [id]
           FROM Request AS r
           FOR XML PATH('request'), TYPE, ROOT('root')
        ) AS tmpXML
)
SELECT d.*
FROM
    tmpCTE AS tmp
    CROSS APPLY tmpXML.nodes('Ids/Id') as Ids(id)
    INNER JOIN DataTable AS d on d.id = Ids.Id.value('.', 'int')
WHERE
    requestid = 35
;

it is a bit redundant, but i think there is some value in breaking down the entire resultset into XML and then breaking it back out into columns; especially across multiple rows.