April 2, 2010 at 10:07 am
I can successfully use the Nodes() and Value() syntax, but I'm having trouble based on the format of this XML file, I think.
In the example, I can acquire Model, Color, Sunroof and Spoiler into a table result set. However, I get stuck on the quantity values.
XML (- instead of <>):
-root-
-cars model="Accord"-
-color- Black -/color-
-sunroof- No -/sunroof-
-spoiler- No -/spoiler-
-quantity shop_location="Los Angeles" qty_avail="100"- -/quantity-
-quantity shop_location="Houston" qty_avail="300"- -/quantity-
-quantity shop_location="New York" qty_avail="150"- -/quantity-
-/cars-
-cars model="Accord"-
-color- Red -/color-
-sunroof- No -/sunroof-
-spoiler- No -/spoiler-
-quantity shop_location="Los Angeles" qty_avail="100"- -/quantity-
-quantity shop_location="Houston" qty_avail="300"- -/quantity-
-quantity shop_location="New York" qty_avail="150"- -/quantity-
-/cars-
-/root-
Here is my query:
SELECT Model.col.value('./@model, 'varchar(20)') [Model],
Model.col.value('./color[1]', 'varchar(20)') [Color],
Model.col.value('./sunroof[1]', 'varchar(20)') [Sunroof],
Model.col.value('./spoiler[1]', 'varchar(20)') [Spoiler],
FROM #tmpGridXml
CROSS APPLY Results.nodes('//model') as Model(col)
Table:
#tmpGridXml (Results xml). There is just a single row in here.
Can anyone lend a hand here? I would greatly appreciate any help!
April 2, 2010 at 10:24 am
I think I found the solution:
SELECT Model.col.value('./@model, 'varchar(20)') [Model],
Model.col.value('./color[1]', 'varchar(20)') [Color],
Model.col.value('./sunroof[1]', 'varchar(20)') [Sunroof],
Model.col.value('./spoiler[1]', 'varchar(20)') [Spoiler],
Model.col.valu('./quantity[1]/@shop_location' ,'varchar(20)') [ShopName1],
Model.col.valu('./quantity[1]/@qty_avail' ,'varchar(20)') [QtyAvail1],
Model.col.valu('./quantity[2]/@shop_location' ,'varchar(20)') [ShopName2],
Model.col.valu('./quantity[2]/@qty_avail' ,'varchar(20)') [QtyAvail2],
FROM #tmpGridXml
CROSS APPLY Results.nodes('//model') as Model(col)
I'm validating the results....
April 2, 2010 at 10:55 am
Try this:
SELECT Car.value('(/cars/@model)[1]', 'varchar(100)'),
Quantity.Node.query('.').value('(/quantity/@shop_location)[1]',
'varchar(100)')
FROM (SELECT Car.Node.query('.') AS Car
FROM #tmpGridXml AS G
CROSS APPLY Results.nodes('/root/cars') Car (Node)) Inr
CROSS APPLY Car.nodes('/cars/quantity') Quantity (Node);
You'll need to add the rest of the value queries, but this should get you started.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply