Post reply

Retrieving a parent attribute value based on its child

  • 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

  • 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

  • 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

  • March 30, 2011 at 8:55 am

    #1305483

    how about this?

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

  • March 30, 2011 at 2:05 pm

    #1305674

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

Viewing 5 posts - 1 through 4 (of 4 total)

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