Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Markzolotoy I guess I'm confused. Couldn't you create a procedure to parse the xml and return the table that you want to load into Power BI?

How are you doing it for SSRS?

Markzolotoy's avatar
Markzolotoy
Impactful Individual
8 years ago

In SSRS I am pulling an XML data into report first, then I use CTE in the report to convert it into the tabluar data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Markzolotoy This sounds like a stored procedure... not SSRS. Either that, or you are doing something I've never done in SSRS before.

    • Markzolotoy's avatar
      Markzolotoy
      Impactful Individual
      8 years ago

      This is some of my SSRS query code:

      DECLARE @xml XML = (select xmlData from @reportData)
;WITH rs AS
	(
		SELECT col.value('(ProjectName)[1]','varchar(max)') AS [ProjectName]
		, col.value('(PointName)[1]','varchar(max)') AS [PointName]
		, col.value('(PointDescription)[1]','varchar(max)') AS [PointDescription]
		, col.value('(Units)[1]','varchar(max)') AS [Units]
		, col.value('(TimeStamp)[1]','varchar(max)') AS [TimeStamp]
		, col.value('(EndDeratingTime)[1]','varchar(max)') AS [EndDeratingTime]
		, col.value('(RealTimeServiceDescription)[1]','varchar(max)') AS [RealTimeServiceDescription]
		, col.value('(CategoryName)[1]','varchar(max)') AS [CategoryName]
		, col.value('(ModeName)[1]','varchar(max)') AS [ModeName]
		, col.value('(Importance)[1]','varchar(max)') AS [Importance]
		, col.value('(AlarmStateIcon)[1]','varchar(max)') AS [AlarmStateIcon]
		FROM @xml.nodes('BadSensorReport/BadSensorDataRecords/BadSensorData') AS tab(col)
	)
	SELECT *, 
		case EndDeratingTime when '' then EndDeratingTime else CONVERT(varchar, CAST(EndDeratingTime AS datetime), 120) end as newDeratingTime, 
		CONVERT(varchar, CAST(TimeStamp AS datetime), 120) as newTimeStamp
	FROM rs
	ORDER BY ProjectName

      That's how xml becomes tabluar data.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Markzolotoy When you connect to SQL in Power BI you can click "advanced" and enter the query in that window... Have you tried that? Does it fail? You could wrap this in a stored procedure or Table Value Function in SQL and call that from the advanced window if the straight query fails.