cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Extract values from xml in column

I've found all sorts of posts that are similar, but nothing seems to work for me.  I have a column that contains xml data.  I need to extract that table data into something usable so I can display it on a dashboard.  

Is there a simple way to make this work?

 

The xml data looks something like this (and resides as text in one of the columns):

<RepeaterData>
<Version/>
<Items>
<Item>
<EvidenceName type="System.String">evidence test</EvidenceName>
<EvidenceDate type="System.DateTime">06/01/2018 00:00:00</EvidenceDate>
<EvidenceType type="System.String">Engagement Indicator</EvidenceType>
<ThreatType type="System.String">Normalization of deviance</ThreatType>
<EvidenceDescription type="System.String">evidence description test</EvidenceDescription>
</Item>
<Item>
<EvidenceName type="System.String">evidence test 2</EvidenceName>
<EvidenceDate type="System.DateTime">05/31/2018 00:00:00</EvidenceDate>
<EvidenceType type="System.String">Analysis of Multiple Data Source</EvidenceType>
<ThreatType type="System.String">Work pressure</ThreatType>
<EvidenceDescription type="System.String">evidence description test 2</EvidenceDescription>
</Item>
</Items>
</RepeaterData>
 
Any ideas how I can get into columns or something easily displayed on a dashboard?
3 REPLIES 3
Microsoft
Microsoft

@slarsson,

I import the above xml data into Power BI Desktop, and get the following column.
1.PNG

After I drill down column step by step, I get the following table eventually, does it return your expected data?
2.PNG

Regards,
Lydia

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

Perhaps a better way to explain the issue:

 

I have a Sharepoint Online data source names 'Signals'.  One of the columns in this sharepoint is called Evidence XML, and contains XML data (the XML provided above), which was generated from a Nintex repeating section form.  So In the case of the example there are two sets of data in the repeating section.  I want to be able to to visually display this data in the report, in such a way that when I filter on the one Signal, the two corresponding Evidence piece described in the EvidenceXML field display as a table/list/whatever-makes-most-sense.   

Hopefully that is clear enough.  Again, new to this, so probalby not explaining it well and probably missing some basic steps.

 

Thanks in a advance for any help.

If I save the xml as a file and add it as a data source, then yes i can eventually drill down to the right level.  The problem is the xml is stored in a text field, so I can't get it to recognize it.  Even when I use the xml file i can only drill down to the right level of data in the Data screen - not in the Report screen.

 

The xml comes from a repeating section in a Nintex Form I built in Sharepoint.  XML is the only way I can access that info, so I was able to get it displayed in a column in Sharepoint.  I'm definitley very new to PowerBI, so not really sure how else to get to the data.  Utlimately I just want to be able to show all the data in the XML somehow on a report/dashboard.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors