Noob xquery problem

Question

Post reply

Noob xquery problem

rho_pooka

Ten Centuries

Points: 1385
More actions
April 6, 2014 at 10:05 pm

#100554

Hi all, I'm still having a hard time writing out xquery statements. I have a better grasp of querying element centric xml, but now that I've turned my attention to attribute centric I'm confused (again). Here's currently what I'm working with:
DECLARE @X XML
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>'
So just to start, how can I see categoryid 9's categoryname, and description? Is it possible? I would assume that the code would look something like this: SELECT @x.query('data(//root[@category=9])'); but that is giving me zero output. Any helps in pointing me in the right direction would be appreciated, I'm feeling stumped!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

The nodes() method of the xml datatype makes this straight forward.

😎

DECLARE @X XML

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') AS RO(CT);

Output

categoryid categoryname description

----------- -------------------- ------------------------------------------------------------

1 Beverages Soft drinks, coffees, teas, beers, and ales

2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings

3 Confections Desserts, candies, and sweet breads

4 Dairy Products Cheeses

5 Grains/Cereals Breads, crackers, pasta, and cereal

6 Meat/Poultry Prepared meats

7 Produce Dried fruit and bean curd

8 Seafood Seaweed and fish

9 Beer Budweiser

10 Liquor Captain Morgan

Russel Loski SSCertifiable Points: 7056 More actions · Answer 2

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

rho_pooka Ten Centuries Points: 1385 More actions · Answer 3

Thanks a ton for the help! Can the answer look different if you wanted to use node navigation? I think that's what I was trying to do the first time, and I really confused myself.

Thanks again,

Ben

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 4

rho_pooka (4/7/2014)
Thanks a ton for the help! Can the answer look different if you wanted to use node navigation? I think that's what I was trying to do the first time, and I really confused myself.
Thanks again,
Ben