Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
johnjbolduc
Helper I
Helper I

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.

1 ACCEPTED 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.

 

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

 

View solution in original post

9 REPLIES 9
johnjbolduc
Helper I
Helper I

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

johnjbolduc
Helper I
Helper I

I fixed the problem by removing the CitationNo column from the table. I may not need that column later on after all.

johnjbolduc
Helper I
Helper I

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.

danextian_0-1737786696029.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
danextian
Super User
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

danextian_0-1737728059102.png

danextian_1-1737728066484.png

Please see the sample queries in the attached pbix.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.