Because I've had so little luck trying to parse my XML with SQL Server XQUERY, and Power BI doesn't do a very good job, I'm currently using a hybrid model, where I parse some of the XML data in SQL using string functions (yeah, I know, but you use what you know; ), and use Power BI for the chunks it handles reasonably well.
Here's the thing. When Power BI parses an XML column, it apparently does so based on the contents of the first record it encounters. Since the XML content varies from record to record, with not all data points necessarily in each XML column, unless the first record's XML contains every data point, the created table will be incomplete. To get around this I needed an XML column that contains all of the relevant data points.
- First I stripped off an image tag that may exist at the end of the XML column (if it exists, it encompasses over 75% of the content, is irrelevant, and may lead to incomplete/incorrect XML).
- After stripping off the image I added appropriate end tags to ensure valid XML.
- At this point Power BI isn't able to parse this complex XML, so I split the XML up into 14 smaller chunks by logical data groupings.
- Added appropriate end tags to ensure valid XML.
- Manually edited the 14 XML columns as needed for a single record with MSPPSDataId = 1, ensuring that every data point existed. Note that only MSPPSDataId = 1 exists in the tables.
- Import the 14 tables into Power BI and transform the data, parse XML, and expand all the tables.
- Finally, I refreshed the database with all of the data records and all is well.
A lot of trial and error went into getting all the pieces working, and it's far from elegant, but it provides me with a repeatable way to get all of the data in a baseline .pbix file.
