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

Question

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

SSC-Addicted

Points: 458

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
;

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

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

Eddie Wuerch SSChampion Points: 12532 More actions · Answer 1

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 · Answer 2

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

DesNorton SSC-Insane Points: 24277 More actions · Answer 3

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.