XML : Why can't I use .nodes() on a subexpression in T-SQL?

I feel like I'm missing something basic and obvious here, but I can't for the life of me figure it out.

Consider this T-SQL:

  declare @tab table (a int, b int);  insert into @tab values (1, 2), (3, 4), (5, 6);  select * from @tab for xml raw, type;    declare @xml xml = (select * from @tab for xml raw, type);  select c.value('@a', 'int') a, c.value('@b', 'int') b  from @xml.nodes('row') t(c);    select c.value('@a', 'int') a, c.value('@b', 'int') b  from (select * from @tab for xml raw, type).nodes('row') t(c);    

It creates a table variable, gives it some data, then SELECTs it FOR XML RAW, TYPE. So far, so good.

Next, it declares an XML variable, and puts the output of that same query into it. Then it queries that variable to return the original table again. Still no problem.

The last line tries to do the same thing, but skipping the extra step of having to declare and populate a variable. No dice — the parser complains that this is a syntax error. I've tried all kinds of extra parentheses and SELECTs and whatnot, to no avail.

How come? What's the right way to do that?

I just know I'm going to feel dumb when someone tells me…

No comments:

Post a Comment