Is it possible to use XML dataset in SSRS report designer

Question

Is it possible to use XML dataset in SSRS report designer ? I need to fetch a large dataset like,

Master Row
  - Details1
  - Details2
  - ... DetailsN

I already created the query which returns me XML data using FOR XML RAW but I am not able to use it in query designer when adding a new report.

Please note I am not using XML data source, I am just trying to add a XML dataset using a SELECT query.. I havn't tried a Stored procedure but not sure if it will work then as well...

In other words, is it possible to add a XML dataset (Not datasource) to SSRS reports

Do I understand you correctly? You want to display an XML's content on a report? How? Are you thinking of an indented output as given in your question? A hardcore approach might be (if you've got the XML already) just to use REPLACE to get rid of all the XML-tags. You might use XSLT to transform your XML to text - but I think it would be easier to create an indented text actually... — Shnugo

Shnugo Shnugo · Accepted Answer · 2016-01-28T22:01:28

If you need an XML looking like a treeview you might do something like this:

DECLARE @xml XML=
'<root>
  <level content="Level A">
    <detail content="Detail AA">
      <item content="item AAA" />
    </detail>
    <detail content="Detail AB">
      <item content="item ABA" />
      <item content="item ABB" />
      <item content="item ABC" />
    </detail>
    <detail content="Detail AC">
      <item content="item ACA" />
      <item content="item ACB" />
    </detail>
  </level>
  <level content="Level B">
    <detail content="Detail BA">
      <item content="item BAA" />
    </detail>
    <detail content="Detail BB">
      <item content="item BBA" />
      <item content="item BBB" />
      <item content="item BBC" />
    </detail>
    <detail content="Detail BC">
      <item content="item BCA" />
      <item content="item BCB" />
    </detail>
  </level>
</root>';


SELECT  CASE The.Node.value('fn:local-name(.)','varchar(max)')
        WHEN 'level' THEN ''
        WHEN 'detail' THEN REPLICATE(' ',4)
        WHEN 'item' THEN REPLICATE(' ',8)
        ELSE ''
        END + The.Node.value('@content','varchar(max)')
FROM @xml.nodes('root//*') AS The(Node)

The result

Level A
    Detail AA
        item AAA
    Detail AB
        item ABA
        item ABB
        item ABC
    Detail AC
        item ACA
        item ACB
Level B
    Detail BA
        item BAA
    Detail BB
        item BBA
        item BBB
        item BBC
    Detail BC
        item BCA
        item BCB

Is it possible to use XML dataset in SSRS report designer

1 Answers