Been 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