Report with XML Data

Question

hi

Currently, I am trying to render XML data in the report in XML format. Unfortunately I can not find a way to render the data correctly formatted cleanly. Is there a way for that? Any kind of help is appreciated

---

at the Moment I use ,replace(cast([xmldata1] as varchar(max)),'><',char(13) + char(10))

but this is more or less ...

Klaus

Answer 1

Hello Klaus,

Looks like your xQuery isn't correct; how does it look like? See XML Query Syntax for XML Report Data (SSRS)

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

hi Olaf,

thx for the fast answer.

the data is inside a SQL Tbl with 2 fields.

the first field has the data as nvarchar(max), the seond field data type is XML and the Data is also XML.

if I run the query the XML Format is not visible on the Report.

the Datasource is not like https://docs.microsoft.com/en-us/sql/reporting-services/report-data/xml-connection-type-ssrs?view=sql-server-ver15.

the datasource is SQL DB,

---

Klaus

Answer 3

Then you can use SQL xQuery to get the data in a structured format, example

CREATE TABLE #TEST(xmldata xml);
GO
INSERT INTO #TEST (xmldata)
VALUES(
N'<Config>
  <Process>
    <UseCase Name="Name of UseCase" Action="run" />
  </Process>
  <Process>
    <UseCase Name="UseCase 2" Action="wait" />
  </Process>
</Config>');
GO

SELECT node.row.value('(.//@Name)[1]', 'varchar(100)') as Name,
       node.row.value('(.//@Action)[1]', 'varchar(100)') as Action
FROM #TEST AS T
     CROSS APPLY
     T.xmldata.nodes(N'//Config/Process/UseCase') AS node(row)
GO

DROP TABLE #TEST;

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 4

Hello Olal,

I'm really thankful for your help!

The translation of the XML into SQL is not that the problem, the problem here is that I have to render the XML structure as well as picture 2 in the report.
If I do that with the XML Query I have the data then in columns but I lose the XML format and that is exactly what should be displayed.

Klaus

---

Klaus

Answer 5

Hello Klaus,

You mean with the hierachy XML structure? I don't think it's possible to achieve in a SSRS Report.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 6

HI Olaf,

yes - that is what the customer is looking for.
thanks for your time and sharing your knowledge.

Klaus

---

Klaus

Answer 7

Thanks for the discussion and knowledge sharing.

---

MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].