XML import is auto-collating nested tables instead of showing them as separate columns

[Rhothgar]

Evening,

I am trying to build an output file which takes its input from the XML instance below, transform the data within and then outputs it to a CSV UTF-8 format.

I receive an XML file in this format:-

<?xml version="1.0" encoding="utf-8"?>
<ArrayOfProductInformation xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

Example of one of the products attributes below:-

    <Attributes>
      <Attribute>
        <Key>GTIN</Key>
        <Value>4084900010211</Value>
        <OrderLocation>1000</OrderLocation>
        <Visible>True</Visible>
        <Name>GTIN</Name>
      </Attribute>
      <Attribute>
        <Key>MAKE</Key>
        <Value>Lyra</Value>
        <OrderLocation>5000</OrderLocation>
        <Visible>True</Visible>
        <Name>Make</Name>
      </Attribute>
      <Attribute>
        <Key>MODEL</Key>
        <Value>Orlow</Value>
        <OrderLocation>3000</OrderLocation>
        <Visible>True</Visible>
        <Name>Model</Name>
      </Attribute>
      <Attribute>
        <Key>TYPE</Key>
        <Value>Graphite Pencils</Value>
        <OrderLocation>4000</OrderLocation>
        <Visible>True</Visible>
        <Name>Type</Name>
      </Attribute>
      <Attribute>
        <Key>QUANTITY</Key>
        <Value>1  Box of 12 Pencils</Value>
        <OrderLocation>5000</OrderLocation>
        <Visible>True</Visible>
        <Name>Quantity</Name>
      </Attribute>
    </Attributes>

Within it are what are to 104 Columns of Attributes: +suffix.

Dufoq3 has written me the perfect query to deal with in on the basis of a table I've selected with an XLSX file.

However, adding this first step as I read up is clearly the best way to go as it will mean I can download the XML file to a local destination and refresh the output file to update all the queries and connections which will eventually be within it.

So again I have fallen at the first hurdle.

1) Opened a blank workbook. Used Get Data From XML to load the file into the spreadsheet.

But the Attribute columns are automatically collated into tables which I cannot find out how to extract.



I've tried al manner of things - Creating Data Type, Expand, Aggregate.

It would help if I knew all the correct jargon and actually knew what to search for.  I'm just clutching at straws.

Perhaps I actually don't need to transform them until I create a query to do the transformation.

All guidance appreciated.

[lbendlin]

You need to decide how you want to traverse the XML hierarchy, and how you want to flatten it into the result table.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.