struggling newbie with xml import

[jstugs]

Good Evening,

I was wondering if someone couild point me in the right direction on how to transform the result of a McAffee Scan to somehting that I can use to display the results in a table. Essentially, I am trying to create a "status" dashboard that will show the results of scans ran on the computer. Eventually, I will want to import multiple files and combine but I am so new to PowerBI that this part is proving troublesome for me.

Here is a sample output of one of the files I am trying to import. What I would like to see if a way to drill down into the individual scans and the result of that file. I know that the Preamble is universal data for the scan, but so is the Date_Time and options as far as I can tell. I just dont really have any idea how to get started. I have read about xml.document vs xml.file but I am still lost. I know that i have to do the transformation in the advanced query editor and also read about i may have to create PK and FK relationship. If there is some resource that I am missing in my search, please point me there and I can figure this out...just not sure how to start.

<?xml version="1.0" encoding="utf-8"?>
<!-- Scan Results -->
<Scan>
	<Preamble>
		<Product_name value="McAfee VirusScan Command Line for Win32" />
		<Version value="6.0.6.6" />
		<License_info value="LICENSED COPY - April 13 2018" />
		<AV_Engine_version value="5800.750" />
		<Dat_set_version value="882" />
	</Preamble>
	<Date_Time value="2018-Apr-13 15:17:32" />
	<Options value="C:\PKI SW /SECURE /NOBREAK /threads 200 /move c:\temp\virus /clean /showcomp /rptall /rpterr /rptcor /streams /xmlpath Fri04-13-2018@15-17-29.03-Externaldrive.xml /report Fri04-13-2018@15-17-29.03-Externaldrive.log " />
	<File name="C:\PKI SW\CAPolicy-inf (pol).txt" status="ok" />
	<File name="C:\PKI SW\CAPolicy-inf (ROOT).txt" status="ok" />
	<File name="C:\PKI SW\activclient_windows_latest (2).zip" status="ok" />
	<Summary On-Path="C:\PKI SW" Total-files="3" Clean="3" Not-Scanned="0" Possibly-Infected="0" Cleaned="0" Moved="0" Deleted="0" />
	<Time value="00:01.22" />
</Scan>

[v-juanli-msft]

Hi @jstugs

When I directly copy the above data to .xml file and connect to it in Power BI Desktop, I get the following table. 

What expected result do you want to get?

what does  “drill down into the individual scans and the result of that file” mean?

Which table contains the actual data? I noticed that one of them listed some file names, do you want to obtain the information of these files, which performs like a drill down action?

Best Regards

Maggie

[jstugs]

Thank you @v-juanli-msft,

I would like to transform that whole file into two flattened relational tables. This file is one execution output of the McAfee tool. Each run of the tool will produce a similar file and the "files" scanned could change depending on the location scanned. The issue that I am having is that most of the data is in the attributes of the elements. I manually transformed the data and was able to get a table that worked but I am trying to automate that exercise. I would like to use one tool to do this and not have to write a script in another program if possible. My idea is to use PowerBI desktop advanced query to pull in all the files (<6 months), transform, and then display the results on a report. Essentially, I would like the data to tranform to something like this:

Read element "value" attribute and transfer to column

Table 1 (main table) - PK (newly generated), Date_time, Options, Summary (each attribute would have own column), Time, Product_name, Version, License_info, AV_Engine_Version, Data_set_version

 Read element "name" and "status" attribute and transfer to their own columns

Table 2 (file table) FK to main table, File name, File status

Any help in the right direction would be greatly appreciated!

**edit** Adding ERD as example