How can I make a multi search SPROC/UDF by passing a tabled-value to it?

Question

I actually want to achieve the following description

This is the table argument I want to pass to the server

<items>
    <item category="cats">1</item>
    <item category="dogs">2</item>
</items>    

SELECT * FROM Item
    WHERE Item.Category = <one of the items in the XML list> 
    AND Item.ReferenceId = <the corresponding value of that item xml element>

--Or in other words:
SELECT FROM Items
    WHERE Item IN XML according to the splecified columns.

Am I clear enought?

I don't mind to do it in a different way other than xml. What I need is selecting values that mach an array of two of its columns' values.

Answer 1

DECLARE @x XML;
SELECt @x = N'<items>
    <item category="cats">1</item>
    <item category="dogs">2</item>
</items>';

WITH shred_xml AS (
  SELECT x.value('@category', 'varchar(100)') AS category,
    x.value('text()', 'int') AS ReferenceId
  FROM @x.nodes('//items/item') t(x) )
SELECT * 
  FROM Item i
  JOIN shred_xml s ON i.category = s.category 
    AND i.ReferenceId = s.ReferenceId;

BTW Doing this from memory, might had got some syntax off, specially at text().

Answer 2

You should be able to parse the XML thus, and join it like a table.

DECLARE @foo XML;

SET @foo = N'<items>
    <item category="cats">1</item>
    <item category="dogs">2</item>
</items>';

WITH xml2Table AS
(
SELECT
    x.item.value('@category', 'varchar(100)') AS category,
    x.item.value('(.)[1]', 'int') AS ReferenceId
FROM
    @foo.nodes('//items/item') x(item)
)
SELECT
    * 
FROM
    Item i
    JOIN
    xml2Table_xml x ON i.category = x.Category AND i.ReferenceId = x.ReferenceId