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
@x.value('(root/production.Categories[@categoryid="9"]/@categoryid)[1]','INT') AS categoryid
,@x.value('(root/production.Categories[@categoryid="9"]/@categoryname')[1],'NVARCHAR(250)') AS categoryname
,@.value('(root/production.Categories[@categoryid="9"]/@description)[1]','NVARCHAR(2048)') AS description
Russel Loski, MCSE Business Intelligence, Data Platform