Retrieving a parent attribute value based on its child

• david James Naylor

  Ten Centuries

  Points: 1026

  More actions

  March 29, 2011 at 8:16 am

  #100758

  Hi All,

  I am having difficulty with an XPath expression and would appreciate some help.

  In the sample XML below i want to return the 'name' attribute of the 'parent' node based on the id of one of its child items.

  This code is used to declare my xml string;

  DECLARE @myXML xml

  SET @myXML = '<root>

  <parent name="p1">

  <item id="1" name="i1"/>

  <item id="3" name="i3"/>

  </parent>

  <parent name="p2">

  <item id="2" name="i2"/>

  <item id="4" name="i4"/>

  </parent>

  </root>'

  I have the ability to select the name from an 'item' node using this query;

  SELECT

  @myXML.query('data(//item[@id=4]/@name)') as itemname

  But when i'm trying to access the parents name value i get the following;

  When i run the following query i get both parents @name attribute values

  SELECT

  @myXML.query('data(//item[@id=4]/../../parent/@name)') as sectionName

  When i run the following query i get the error shown below;

  SELECT

  @myXML.query('data(//section/@name./item[@id=sql:column("esi.ItemID")])')

  XQuery [query()]: There is no element named 'item' in the type 'attribute(name.,xdt:untypedAtomic) *'.

  Any help would be greatly recieved.

  Thank you

• WayneS

  SSC Guru

  Points: 95461

  More actions

  March 29, 2011 at 8:50 am

  #1304930

  How's this?

  ;WITH cte AS

  (

  SELECT [parent] = d.v.value('../@name[1]', 'varchar(50)'),

  [child] = d.v.value('@id[1]', 'varchar(50)')

  FROM @myXML.nodes('/root/parent/item') AS d(v)

  )

  SELECT [parent]

  FROM cte

  WHERE child = '4'

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• david James Naylor

  Ten Centuries

  Points: 1026

  More actions

  March 30, 2011 at 2:36 am

  #1305295

  Thanks Wayne,

  I have one problem with this approach, i', trying to make my Xquery selection alongside other standard sql column selections and this doesnt work with your solution. i will look at created a function to to do the CTE work unless you have any other suggestions?

  Thanks

• !2Brite

  SSC Veteran

  Points: 219

  More actions

  March 30, 2011 at 8:55 am

  #1305483

  how about this?

  SELECT @myXML.query('data(//item[@id=4]/../@name)') as itemname

• david James Naylor

  Ten Centuries

  Points: 1026

  More actions

  March 30, 2011 at 2:05 pm

  #1305674

  i swear i tried that! cant have though because it works fine, thanks!