Working with XML data

Question

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 pasting T-SQL is not possible.

Thanks

anonymous · Answer

Markzolotoy&nbsp;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 · Answer

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

anonymous · Answer

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

markzolotoy · Answer

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.

anonymous · Answer

Markzolotoy&nbsp;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.

Xml.Document	Returns the contents of an XML document as a hierarchical table (list of records).
Xml.Tables	Returns the contents of an XML document as a nested collection of flattened tables.

Forum Discussion

Working with XML data

6 Replies

Related content

Overwrite not working in Copy Data?

Fabric Data Agent Tool not working

Notebook concurrency in data pipelines not working

Fabric & Azure Data User Group Program Requirements & Benefits

CubeValue formula to data model - only works partially