Forum Discussion
Working with XML data
I need to show xml data in my report. This data is stored in an SQL table. How do I extract parts of this xml and convert it into a tabular data? I am doing it in my SSRS report, but copying and past...
Markzolotoy This sounds like a stored procedure... not SSRS. Either that, or you are doing something I've never done in SSRS before.
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 ProjectNameThat's how xml becomes tabluar data.
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.