• You can use the where or put the query into your XQuery (I don't know which is more efficient):

    DECLARE @X XML;

    DECLARE @CategoryID int = 9;

    SET @X = '<root>

    <production.Categories categoryid="1" categoryname="Beverages"

    description="Soft drinks, coffees, teas, beers, and ales" />

    <production.Categories categoryid="2" categoryname="Condiments"

    description="Sweet and savory sauces, relishes, spreads, and seasonings" />

    <production.Categories categoryid="3" categoryname="Confections"

    description="Desserts, candies, and sweet breads" />

    <production.Categories categoryid="4" categoryname="Dairy Products"

    description="Cheeses" />

    <production.Categories categoryid="5" categoryname="Grains/Cereals"

    description="Breads, crackers, pasta, and cereal" />

    <production.Categories categoryid="6" categoryname="Meat/Poultry"

    description="Prepared meats" />

    <production.Categories categoryid="7" categoryname="Produce"

    description="Dried fruit and bean curd" />

    <production.Categories categoryid="8" categoryname="Seafood"

    description="Seaweed and fish" />

    <production.Categories categoryid="9" categoryname="Beer"

    description="Budweiser" />

    <production.Categories categoryid="10" categoryname="Liquor"

    description="Captain Morgan" />

    </root>'

    SELECT

    RO.CT.value('@categoryid','INT') AS categoryid

    ,RO.CT.value('@categoryname','NVARCHAR(250)') AS categoryname

    ,RO.CT.value('@description','NVARCHAR(2048)') AS description

    FROM @X.nodes

    ('root/production.Categories[@categoryid=sql:variable("@CategoryID")]')

    AS RO(CT)

    Russel Loski, MCSE Business Intelligence, Data Platform