xml value() in join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

xml value() in join

Author

Topic

qshi
Starting Member

3 Posts

Posted - 2009-12-09 : 17:08:18

Hi,

I am new to SQL 2005, and trying to explore the xquery and now having a puzzling problem.

I copied a query from the following link:
[url]http://www.sommarskog.se/arrays-in-sql-2005.html#XML[/url]
and modified as following:

CREATE PROCEDURE [dbo].[usp_GetEvents]
@ItemIDs xml
As
SET NOCOUNT ON
Select *
From dbo.Events E
Inner Join @ItemIDs.nodes('/Items/Item') as I(Item)
On E.ItemID = I.Item.value('@Value','int')

the table Events has a int ItemID column. the @ItemIDs xml passed would be like '<Items><Item Value="1"/><Item Value="2"/></Items>

when excuting, the management studio produced the following error:
Msg 493, Level 16, State 1, Procedure usp_GetEvents, Line 10
The column 'Item' that was returned from the nodes() method cannot be used directly. It can only be used with one of the four xml data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks.

I can not find a solution from the internet. Any help would be greatly appriciated. I am using SQL 2005 SP2.

Thanks,

Q

Sachin.Nand

2937 Posts

Posted - 2009-12-10 : 00:54:50

Select *
From dbo.Events E inner join
(
select I.Item.value('@Value','int')as value from @x.nodes('/Items/Item') as I(Item)
)T on T.value=E.ItemID

PBUH

qshi
Starting Member

3 Posts

Posted - 2009-12-10 : 09:17:14

HI PBUH,

This works. Thanks.

Any reason why the SQL in the original post is not working? I am also trying to understand the logic behind.

Thanks,

Q

quote:
Originally posted by Idera

Select *
From dbo.Events E inner join
(
select I.Item.value('@Value','int')as value from @x.nodes('/Items/Item') as I(Item)
)T on T.value=E.ItemID

PBUH

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-10 : 10:35:03

Hi qshi

The specific issue in your original query is the '*'. That will ask it to try to return all fields from all the things you've joined together (including the 'Item' column from the XML). But the 'Item' column cannot be used directly (as the error says).

If you replace the '*' with 'e.*', it will work just fine. Here's some code to demonstrate:

declare @ItemIDs xml
set @ItemIDs = '<Items><Item Value="1"/><Item Value="2"/></Items>'

declare @Events table (ItemId int, ColA varchar(20))
insert @Events
          select 1, 'Hello World'

--works
Select e.*
From @Events E 
Inner Join @ItemIDs.nodes('/Items/Item') as I(Item)
On E.ItemID = I.Item.value('@Value','int')

--also works
Select e.*, I.Item.value('@Value','int')
From @Events E 
Inner Join @ItemIDs.nodes('/Items/Item') as I(Item)
On E.ItemID = I.Item.value('@Value','int')

/* fails
Select *
From @Events E 
Inner Join @ItemIDs.nodes('/Items/Item') as I(Item)
On E.ItemID = I.Item.value('@Value','int')
*/

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

qshi
Starting Member

3 Posts

Posted - 2009-12-10 : 12:05:44

Hi Ryan,

Thank you very much. That's it. That is what I was looking for.

Q

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources