Daniel_L Daniel_L - 1 month ago 10
SQL Question

Use a casted column name in a join query in sql-server

Say I have a query like that casts an xml query

SELECT TOP 5
CAST (row.query('col(/xml/tag)'), as NVARCHAR(100)) AS 'foobar'
FROM db.table


How can I use the casted result of the xml query in a
LEFT JOIN
?

SELECT TOP 5
CAST (row.query('col(/xml/tag)'), as NVARCHAR(100)) AS 'foobar'
FROM db.table
LEFT JOIN db.table_table
-- don't know the syntax for this part
ON db.table['foobar'] = db.table_table.col

Answer

Make the original query as sub-select

SELECT foobar
FROM   (SELECT TOP 5 Cast (row.query('col(/xml/tag)') AS NVARCHAR(100)) AS foobar
        FROM   db.TABLE) t
       LEFT JOIN db.table_table
              ON t.foobar = db.table_table.col