Shred XML and show in SSRS Report

Question

I have XML like so

<OrchestrationConfiguration>
<Instrument>
<Spectrometer>
    <Inputs>
      <Channel-ChannelNumber-1>
        <ChannelNumber>1</ChannelNumber>
        <ChannelName>Answer01</ChannelName>
      </Channel-ChannelNumber-1>
    </Inputs>
    <Outputs>
      <Channel-ChannelNumber-1>
        <ChannelNumber>1</ChannelNumber>
        <ChannelName>1</ChannelName>
        <ChannelName>2</ChannelName>
        <ChannelName>3</ChannelName>
        <ChannelName>4</ChannelName>
        <ChannelName>5</ChannelName>
        <ChannelName>6</ChannelName>
        <ChannelName>7</ChannelName>
        <ChannelName>8</ChannelName>
        <ChannelName>9</ChannelName>
        <ChannelName>10</ChannelName>
      </Channel-ChannelNumber-1>
      <Channel-ChannelNumber-2>
      <ChannelNumber>2</ChannelNumber>
      <ChannelName>Temperature-K</ChannelName>
      </Channel-ChannelNumber-2>
      <Channel-ChannelNumber-3>
        <ChannelNumber>3</ChannelNumber>
        <ChannelName>Pressure-inHg-absolute</ChannelName>
        </Channel-ChannelNumber-3>
    </Outputs>
</Spectrometer>
</OrchestrationConfiguration>

In my SSRS report. I have created a list grouped by Parent Name and to get the outputs I have applied the following filters

The issue I am having is to get the Inputs. I am unable to distinguish between Outputs and Inputs as the filter is the same. Is there any other way of doing this? I am happy to change the format of XML if required.

I am using the XMLTable function to shred the xml. Shred XML

Notes which might help

I am using SQL Server 2014.
The above XML is present in a database table in SQL.

Is the construction of this XML under your control? You should really avoid to *name-number* your elements like in ``. Rather use an attribute like ``. You will get into severe problems otherwise... — Shnugo, Jun 26 '17 at 22:33
Yes the XML can be changed. It has to be changed for all instruments (instrument block). Oh thank you, what kind of issues? — Abe, Jun 27 '17 at 09:50

score 0 · Answer 1 · answered Jun 28 '17 at 08:41

In your comment you state, that the XML can be changed. You really should do this.

Some background:

The order of nodes in XML is persisted. There is no need to state the "position" as long as it is the same as the actual position within the node order. Still it can be a good idea to state a ChannelNumber literally, as it can simplify the queries.
It is easy to ask an XML *Show me all <Channel> elements below <Outputs>, but it needs hacky workarounds to ask Show me all elements below <Outputs>, which have a name starting with Channel.

Try it like this:

DECLARE @XML XML=
'<OrchestrationConfiguration>
  <Instrument>
    <Spectrometer>
      <Inputs>
        <Channel Number="1">
          <ChannelName>Answer01</ChannelName>
        </Channel>
      </Inputs>
      <Outputs>
        <Channel Number="1">
          <ChannelName>1</ChannelName>
          <ChannelName>2</ChannelName>
          <ChannelName>3</ChannelName>
          <ChannelName>4</ChannelName>
          <ChannelName>5</ChannelName>
          <ChannelName>6</ChannelName>
          <ChannelName>7</ChannelName>
          <ChannelName>8</ChannelName>
          <ChannelName>9</ChannelName>
          <ChannelName>10</ChannelName>
        </Channel>
        <Channel Number="2">
          <ChannelName>Temperature-K</ChannelName>
        </Channel>
        <Channel Number="3">
          <ChannelName>Pressure-inHg-absolute</ChannelName>
        </Channel>
      </Outputs>
    </Spectrometer>
  </Instrument>
</OrchestrationConfiguration>';

--I do not know, how you need the data, but with SQL-Server I'd implement an inline table valued function, passing in the XML and retrieving the data as derived table. Use something like this:

SELECT InpCh.value('@Number','int') AS InputChannelNumber
      ,InpCh.value('(ChannelName/text())[1]','nvarchar(max)') AS InputChannelName
      ,OutCh.value('@Number','int') AS InputChannelNumber
      ,OutChName.value('(text())[1]','nvarchar(max)') AS InputChannelName
FROM @xml.nodes('/OrchestrationConfiguration/Instrument/Spectrometer/Inputs/Channel') AS A(InpCh)
OUTER APPLY @xml.nodes('/OrchestrationConfiguration/Instrument/Spectrometer/Outputs/Channel') AS B(OutCh)
OUTER APPLY B.OutCh.nodes('ChannelName') AS C(OutChName)

Shred XML and show in SSRS Report

Notes which might help

1 Answers1