Multipart identifer could not be bound, correlated subquery

Question

This is a contrived example using SQL Server 2008.

I'm essentially storing a list of ids in an xml column in a table: temp (bigint id, xml ids)

I want to join the table itself to the xml nodes.

So far I have:

select * from temp x
join (
    select  x.id
    ,   ids.id.value('@value', 'bigint') zid 
    from    temp t cross apply ids.nodes('/ids/id') as ids(id)
) z on x.id=z.id

I get: The multi-part identifier "x.id" could not be bound.

This just looks like a normal correlated subquery to me. What am I missing?

----- update:

For the following sample data in the temp table:

id ids
-- ---
 1 <ids><id value="11" /><id value="12" /><id value="13" /></ids>
 2 <ids><id value="21" /><id value="22" /><id value="23" /></ids>
 3 <ids><id value="31" /><id value="32" /><id value="33" /></ids>

I would expect to see the following as the result set:

id zid
-- ---
 1  11
 1  12
 1  13
 2  21
 2  22
 2  23
 3  31
 3  32
 3  33

Answer 1

You are selecting x.id inside the subquery, maybe you should be selecting t.id?

Answer 2

It is a derived table not a correlated sub query. Note this doesn't work either.

WITH TEMP AS
(
SELECT 1 AS id
)

select x.id 
from temp x
join (
select  x.id,*  from temp t
) z
on  x.id  =  z.id 

From MSDN derived_table

Is a subquery that retrieves rows from the database. derived_table is used as input to the outer query.

so this is quite different to a correlated sub query where

the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

To get your expected output you don't need a JOIN at all.

WITH TEMP AS
(
select 1 as id, CAST('<ids><id value="11" /><id value="12" /><id value="13" /></ids>' as xml) as ids UNION ALL
select 2, '<ids><id value="21" /><id value="22" /><id value="23" /></ids>' UNION ALL
select 3, '<ids><id value="31" /><id value="32" /><id value="33" /></ids>'

)

 select 
 t.id,
 ids.id.value('@value', 'bigint') zid 
 from    temp t cross apply ids.nodes('/ids/id') as ids(id)

If you did decide to use one then despart's answer was correct.

select x.id, zid  from temp x
join (
    select 
    t.id,
    ids.id.value('@value', 'bigint') zid 
    from    temp t cross apply ids.nodes('/ids/id') as ids(id)
) z on x.id=z.id