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

  • beth-447786

    SSC Veteran

    Points: 296


    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>
    <Hyperlink>=Fields!ihurl.Value</Hyperlink>

    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

    ;with
    xmlnamespaces (
    default 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition',
    'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner' as rd
    )
    select
    a.Server, a.[Report],
    Hyperlink = w.value('(Hyperlink)[1]', 'varchar(100)')
    from (
    select
    @@servername as 'Server', [Path] as 'Report',
    cast(cast(c.Content as varbinary(max)) as xml) as 'ContentXML'
    from
    dbo.Catalog c with (nolock)
    where
    c.Content is not null
    and c.ItemID = '[redacted]'
    ) a
    cross apply ContentXML.nodes(
    '//ActionInfo/Actions/Action'
    ) hl(w)
    where
    w.value('(Hyperlink)[1]', 'varchar(100)') is not null
    ;
  • Eddie Wuerch

    SSChampion

    Points: 12256

    The simplest (but not the most efficient) way is to just crawl up the nodes with ../ to move up each level

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

    Eddie Wuerch
    MCM: SQL

  • beth-447786

    SSC Veteran

    Points: 296

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

  • DesNorton

    SSC-Insane

    Points: 22440

    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;


    How to post data/code on a forum to get the best help.[/url]
    Make sure that you include code in the appropriate IFCode tags.

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

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