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