Solved: XML import only imports first line

Ben_Ges · ‎08-20-2021

Hi,

I am trying to import a .xml into Power Query in Excel. The file has the following structure:

<?xml version="1.0" encoding="UTF-8" ?>
<?xml-stylesheet type="text/xsl" href="retainvarsexport.xsl"?>
<Recipe Version="1.0">
<Header>
<Type>retainvarsexport</Type>
<Name></Name>
<Count>18095</Count>
<Modified>2021-07-20T10:59:00</Modified>
<ProjectName>Project 1</ProjectName>
<ProjectVersion>3.x.55.0</ProjectVersion>
<GeneratorName>Epam4</GeneratorName>
<GeneratorVersion>1.2.0.10733</GeneratorVersion>
</Header>
<Body>
<Variable Name="Variable 1" Type="BYTE">0</Variable>
<Variable Name="Variable 2" Type="BYTE">0</Variable>
<Variable Name="Variable 3" Type="BOOL">0</Variable>
<Variable Name="Variable 4" Type="BYTE">0</Variable>
<Variable Name="Variable 5" Type="REAL">0</Variable

<Variable Name="Variable 6" Type="BOOL">0</Variable>
</Body>
</Recipe>

When I import the file into Power Query, I only get the following:

Unfortunately I am not able to import the variables.

When I change the code to this:

<Body>
<Variable Name="Variable 1" Type="BYTE">0</Variable>
<Variable Name="Variable 2" Type="BYTE">0</Variable>
<Variable Name="Variable 3" Type="BOOL">0</Variable>
<Variable Name="Variable 4" Type="BYTE">0</Variable>
<Variable Name="Variable 5" Type="REAL">0</Variable

<Variable Name="Variable 6" Type="BOOL">0</Variable>
</Body>

I am able to import the data in the way I want:

It works, but I need to change the code in the .xml.

Is there a way to import the data without modifying the code?

I look forward to suggestions.

And sorry if this question seems foolish, I am totally new to Power Query... 😄

mahoneypat · ‎08-20-2021

In your first pic, just click on the word Table in the Body column to drill to your variables.

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

View solution in original post

mahoneypat · ‎08-20-2021

In your first pic, just click on the word Table in the Body column to drill to your variables.

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

Anonymous · ‎11-01-2021

Hi Pat,

I have a similar problem. But I would like to import all tables (not only variables of one column). My xml file is structured hierarchical containing lots of tables.

Is there a simple way to import all tables?

Best regards, Jacob