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.