Get Data from XML Nodes for multiple rows

• pricejt

  Say Hey Kid

  Points: 671

  More actions

  November 23, 2011 at 12:09 pm

  #248541

  I apologize in advance if you see this posted in two spots. I believe i posted this in a dead thread before.

  Ok i have this working however I still don't understand how to get all the values when my select returns more than 1 row. I assume I need a loop just not sure if there is a better way.

  Here is what I have:

  DECLARE @x XML;

  SELECT @x =

  r.XMLData

  FROM RulesetGroups rg

  INNER JOIN Rulesets rs on

  rg.RulesetGroupID = rs.RulesetGroupID

  INNER JOIN Rules r on

  rs.RulesetID = r.RulesetID

  WHERE r.RuleTypeID = 3 and r.XMLData.value('(/Screen/ScreenOption/@Name)[1]', 'varchar(255)') is not null;

  -- select "Name"

  WITH Num(i)

  AS

  (

  SELECT 1

  UNION ALL

  SELECT i + 1

  FROM Num

  WHERE i < (SELECT @x.value('count(/Screen/ScreenOption/@Name)','varchar(255)') )

  )

  SELECT x.value('@Name[1]', 'varchar(20)')

  FROM Num

  CROSS APPLY @x.nodes('/Screen/ScreenOption[position()=sql:column("i")]') e(x);

  My XML for one row. I will have hundreds of these though in different rows:

  <Screen Title="">

  <ScreenOption Sequence="1" Name="BTWidth" />

  <ScreenOption Sequence="2" Name="BTHeight" />

  </Screen>

  Someone responded with this however I don't understand it. Is this meant to replace everything from above? If so i get an error even with xmldata.nodes portion saying you can't alias it.

  SELECT t.u.value('@Sequence','int') as Sequence,

  t.u.value('@Name','varchar(100)') as Name

  FROM RulesetGroups rg

  INNER JOIN Rulesets rs on

  rg.RulesetGroupID = rs.RulesetGroupID

  INNER JOIN Rules r on

  rs.RulesetID = r.RulesetID

  CROSS APPLY XMLData.Nodes('/Screen/ScreenOption')t(u)

  WHERE r.RuleTypeID = 3 and t.u.value('@Name', 'varchar(255)') is not null

  ORDER BY t.u.value('@Sequence','int')

  Any help will be much appreciated.

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  November 23, 2011 at 12:40 pm

  #1412428

  XML is case-sensitive. That appears to include the .nodes() function.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• pricejt

  Say Hey Kid

  Points: 671

  More actions

  November 23, 2011 at 12:49 pm

  #1412433

  wow that is awesome.

  Thank you so much.

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  November 23, 2011 at 8:18 pm

  #1412539

  drew.allen (11/23/2011)

  ---

  XML is case-sensitive. That appears to include the .nodes() function.

  Drew

  The first time I found that out, my thought was "Great... first, mandatory semi-colons and now mandatory casing. It's getting to be more like Oracle every day."

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)