Forum Discussion
XML Parsing Missing Column
Hi all, and thanks in advance for any help you can provide.
I'm fairly new to Power BI and am having problems parsing a particular XML column. The XML is:
<dcjis:DataExchange xmlns:dcjis="http://www.xfact.com/schemas/eopss/dcjis/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dcjis-ext="http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension" xmlns:dot-ec="http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension" xmlns:nc="http://niem.gov/niem/niem-core/2.0" xmlns:j="http://niem.gov/niem/domains/jxdm/4.0" xmlns:s="http://niem.gov/niem/structures/2.0">
<dot-ec:Vehicle s:id="vehicle1">
<nc:VehicleColorPrimaryCode>BLU</nc:VehicleColorPrimaryCode>
<nc:ItemMakeName>VOLK</nc:ItemMakeName>
<nc:ItemModelName>JETTA</nc:ItemModelName>
<nc:ItemModelYearDate>2011</nc:ItemModelYearDate>
<nc:VehicleCMVIndicator>false</nc:VehicleCMVIndicator>
<nc:ConveyanceRegistration>
<nc:ConveyanceRegistrationPlateIdentification>
<nc:IdentificationID>184ZM4</nc:IdentificationID>
<nc:IdentificationExpirationDate>
<nc:Date>2020-04-30</nc:Date>
</nc:IdentificationExpirationDate>
<j:DrivingJurisdictionAuthorityANSID20Code>MA</j:DrivingJurisdictionAuthorityANSID20Code>
<nc:IdentificationJurisdictionFIPS10-4Code>US</nc:IdentificationJurisdictionFIPS10-4Code>
</nc:ConveyanceRegistrationPlateIdentification>
<dot-ec:VehiclePlateTypeCode>PAN</dot-ec:VehiclePlateTypeCode>
<dot-ec:VehiclePlateTypeText>Passenger Normal</dot-ec:VehiclePlateTypeText>
</nc:ConveyanceRegistration>
<dot-ec:VehiclePassenger16PlusIndicator>false</dot-ec:VehiclePassenger16PlusIndicator>
<dot-ec:HazMatPlacardDisplayedIndicator>false</dot-ec:HazMatPlacardDisplayedIndicator>
<dot-ec:VehicleRegistrationUnknownIndicator>false</dot-ec:VehicleRegistrationUnknownIndicator>
<nc:InsuranceCarrierName>GOVT EMPLOYEE INS</nc:InsuranceCarrierName>
</dot-ec:Vehicle>
</dcjis:DataExchange>
When I parse and expand the XML from a SQL Server table in Power BI it excludes the VehiclePlateTypeText column. The SQL Server table contains three columns:
- MSPPSDataID
- CitationNo
- Vehicle (XML)
However, if I save the XML only to a text file and load that into Power BI it parses and expands correctly to include the VehiclePlateTypeText column. This is not a useable workaround, as I have to pull the XML from SQL Server.
Any idea why this happens? Does anyone know if it's possible to force Power BI to get all of the XML data? Thanks.
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.
9 Replies
Hi johnjbolduc
I tried treating your XML as a column instead of as a stand alone data source and I was abl to get the VehiclePlateTypeCode data
Please see the sample queries in the attached pbix.
Hi danextion -
Thanks for the quick response. Unfortunately I have to get Power BI Desktop updated, as my version (V2.138.1452.0) can't load your pbix file. Not sure how quickly I can get the update, as it has to go throug central IT.
Please see the attached text file.
OK, my ignorance of Power BI is showing. I have no idea how to use the text file.
Hi johnjbolduc
The text file has two queries in. Each starts with //query name. Create a blank query in the query editor and delete everything inside and then paste a query to it. You will see a sample transformation.
I fixed the problem by removing the CitationNo column from the table. I may not need that column later on after all.
I found a workaround to the issue. Although none of the answers provided a solution, this thread can be closed.
Hi johnjbolduc ,
Thank you for your feedback. If it is convenient, could you please share your workaround here? This would be very helpful for others in the community who might encounter a similar issue. We appreciate your contribution.
Best Regards
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.
