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

• beth-447786

  SSC-Addicted

  Points: 458

  More actions

  April 24, 2019 at 7:10 pm

  #3532071

  
  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: 12538

  More actions

  April 24, 2019 at 8:38 pm

  #3532394

  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-Addicted

  Points: 458

  More actions

  April 24, 2019 at 9:35 pm

  #3532640

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

• DesNorton

  SSC-Insane

  Points: 24472

  More actions

  April 25, 2019 at 7:30 am

  #3533987

  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.