none
Report with XML Data RRS feed

  • 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


    Tuesday, October 15, 2019 11:02 AM

Answers

All replies

  • 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]

    Tuesday, October 15, 2019 11:27 AM
  • 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


    Tuesday, October 15, 2019 12:19 PM
  • 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]

    Tuesday, October 15, 2019 12:44 PM
  • 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

    Tuesday, October 15, 2019 1:47 PM
  • 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]

    Tuesday, October 15, 2019 2:24 PM
  • HI Olaf,

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

    Klaus


    Klaus

    Tuesday, October 15, 2019 2:31 PM
  • 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 MSDNFSF@microsoft.com.

    Wednesday, October 16, 2019 2:57 AM