Parsing XML element values in Power Query

[sc20d2]

I need to parse some XML with data stored in element values (rather than attributes) in Power Query. This works fine as long as the elements don't have sub-elements as follows:

<people>
    <person id="1" gender="male">
        <name>Joe</name>
    </person>
    <person id="2" gender="female">
        <name>Sally</name>
    </person>
    <person id="3" gender="male">
        <name>Mike</name>
    </person>
</people>

When my element has sub-elements, Power Query assumes the column is a table datatype and ignores the element values (Joe, Sally, and Mike). It forces me to drill down into the table and provides access to the sub-element's attributes (abbreviated name, full name), which is not what I'm interested in.

<people>
    <person id="1" gender="male">
        <name>Joe<type value="abbreviated name" />
        </name>
    </person>
    <person id="2" gender="female">
        <name>Sally<type value="full name" />
        </name>
    </person>
    <person id="3" gender="male">
        <name>Mike<type value="abbreviated name" />
        </name>
    </person>
</people>

I don't need the data in the sub-elements, so any advice on removing them, parsing them, or deleting them would be much appreciated. 

[BA_Pete · Accepted solution]

Hi @sc20d2 ,

Open your query in Advanced Editor then adjust the Source step to change Xml.Tables to Xml.Document.

You'll still need to do a bit of jiggery-pokery to get the data into the shape you need, but this will at least expose all the data elements for use.

Pete

[sc20d2]

Ah, I think I see that now. Thanks very much for the pointer @BA_Pete . That was exactly what I needed! 

[BA_Pete]

No problem, happy to help.

Don't forget to give a thumbs-up on any posts that have helped you ‌‌👍

Pete