Query XML Column With Cross Apply

• JuanBob

  SSCertifiable

  Points: 5059

  More actions

  April 2, 2010 at 10:24 am

  #1144918

  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....

• GSquared

  SSC Guru

  Points: 260824

  More actions

  April 2, 2010 at 10:55 am

  #1144944

  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