nodes does not seem to work when xml value is created through a select

Dear all,
While
DECLARE @.x xml
SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
SELECT (select @.x).query('//a')
works fine, a very similar query
DECLARE @.x xml
SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
SELECT t.c.query('.') from (select @.x).nodes('//a') t(c)
fails with
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Anyone knows whether this is expected/documented behaviour?
Thanks
PeterDerived table syntax in FROM clause requires table alias to follow it
(unlike scalar sub-query in SELECT).
You can achieve what you want with CROSS APPLY:
DECLARE @.x xml
SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
SELECT t2.c.query('.') from (select @.x) t1(x) CROSS APPLY t1.x.nodes('//a')
t2(c)
Best regards,
Eugene
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter" <pgp.coppens@.gmail.com> wrote in message
news:1138820267.235233.39270@.f14g2000cwb.googlegroups.com...
> Dear all,
> While
> DECLARE @.x xml
> SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
> SELECT (select @.x).query('//a')
> works fine, a very similar query
> DECLARE @.x xml
> SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
> SELECT t.c.query('.') from (select @.x).nodes('//a') t(c)
> fails with
> Msg 102, Level 15, State 1, Line 3
> Incorrect syntax near '.'.
> Anyone knows whether this is expected/documented behaviour?
> Thanks
> Peter
>|||That helps,
Thanks,
Peter.

nodes does not seem to work when xml value is created through a select

Dear all,
While
DECLARE @.x xml
SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
SELECT (select @.x).query('//a')
works fine, a very similar query
DECLARE @.x xml
SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
SELECT t.c.query('.') from (select @.x).nodes('//a') t(c)
fails with
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Anyone knows whether this is expected/documented behaviour?
Thanks
Peter
Derived table syntax in FROM clause requires table alias to follow it
(unlike scalar sub-query in SELECT).
You can achieve what you want with CROSS APPLY:
DECLARE @.x xml
SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
SELECT t2.c.query('.') from (select @.x) t1(x) CROSS APPLY t1.x.nodes('//a')
t2(c)
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter" <pgp.coppens@.gmail.com> wrote in message
news:1138820267.235233.39270@.f14g2000cwb.googlegro ups.com...
> Dear all,
> While
> DECLARE @.x xml
> SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
> SELECT (select @.x).query('//a')
> works fine, a very similar query
> DECLARE @.x xml
> SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
> SELECT t.c.query('.') from (select @.x).nodes('//a') t(c)
> fails with
> Msg 102, Level 15, State 1, Line 3
> Incorrect syntax near '.'.
> Anyone knows whether this is expected/documented behaviour?
> Thanks
> Peter
>
|||That helps,
Thanks,
Peter.