Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
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.
Solved! Go to Solution.
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.
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.
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.
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.
I fixed the problem by removing the CitationNo column from the table. I may not need that column later on after all.
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.
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |