Joining to xml data in SELECT for SQL2005

posted in: Uncategorized | 0

BronwenWeeGo.jpgBeen playing with using supplied XML to join to tables in a select statement.  So i thought I’d do a simple example to remind myself next time i need to do it..

So i have the following xml structure:

<Categories>
  <id>3</id>
  <id>6</id>
  <id>15</id>
</Categories>

So to use it in a proc I do the following:

ALTER PROCEDURE [dbo].[GetItemsByCategories] 
	@Categories xml

AS
BEGIN

SET NOCOUNT ON;
SELECT 
    	i.ItemName
FROM 
	[dbo].[Item] i
INNER JOIN 
	[dbo].[Category] c ON i.[CategoryID] = c.[CategoryID]
INNER JOIN
	@Categories.nodes('/Categories/id') as ParamValues(ID) 
	ON c.CategoryID = ParamValues.ID.value('.', 'int')
END