Working with XML data

[Markzolotoy]

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

[v-shex-msft]

Hi @Markzolotoy,

I'd like to suggest you enter to query editor, add a custom column and try to use xml.document/xml.table to analysis this xml column, then expand the analysed xml table.

Accessing data functions

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.

Regards,

Xiaoxin Sheng

[Seth_C_Bauer]

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

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.

[Seth_C_Bauer]

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

[Markzolotoy]

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.

[Seth_C_Bauer]

@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.