Is there a way to walk "up" from current XML node

  • Hello,

    I’m attempting to get some information from SSRS RDL’s.  Is there a way to back up from the current location in an XML document? Given this XML definition fragment;
    - <TablixRow> 
    - <TablixCells>
    - <TablixCell>
    - <CellContents>
    - <Textbox Name="PrefFullName">
    - <ActionInfo>
    - <Actions>
    - <Action>

    I’ve figured out how to pluck out the <Hyperlink> value, but I cannot figure how to back up to the <Textbox element to get the name. Below is the code that gets Hyperlink. Any help is appreciated.

    thanks, beth

    xmlnamespaces (
    default '',
    '' as rd
    a.Server, a.[Report],
    Hyperlink = w.value('(Hyperlink)[1]', 'varchar(100)')
    from (
    @@servername as 'Server', [Path] as 'Report',
    cast(cast(c.Content as varbinary(max)) as xml) as 'ContentXML'
    dbo.Catalog c with (nolock)
    c.Content is not null
    and c.ItemID = '[redacted]'
    ) a
    cross apply ContentXML.nodes(
    ) hl(w)
    w.value('(Hyperlink)[1]', 'varchar(100)') is not null
  • The simplest (but not the most efficient) way is to just crawl up the nodes with ../ to move up each level

    a.Server, a.[Report],
    Hyperlink = w.value('(Hyperlink)[1]', 'varchar(100)'),
    TextBoxName = w.value('(../../@Name)[1]', 'varchar(100)')

    Eddie Wuerch
    MCM: SQL

  • thank you Eddie, that works. I realize it might break if the number of nodes up should change. regards, beth

  • This should be a bore performant solution

    SELECT      Hyperlink = w.value( '(ActionInfo/Actions/Action/Hyperlink/text())[1]', 'varchar(100)' )
    , TextBoxName = w.value('@Name', 'varchar(100)')
    FROM (
    SELECT ContentXML ...
    ) AS a
    CROSS APPLY ContentXML.nodes( '//Textbox' ) AS hl(w)
    WHERE w.value( '(ActionInfo/Actions/Action/Hyperlink/text())[1]', 'varchar(100)' ) IS NOT NULL;

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

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