XML Source - Getting child value with XQuery

[max_totara]

Hi,

I have an XML file as a source, I can easily get every row's elements values and simple agregates for direct decendents of the rows. I would like to bring some values from further down into the row's level by agregating them (using First or something like that). Is there a way to invoke an XQuery to retrieve the values I want? I tried to create a custom function, but couldn't manage to find any XML functions.

With the example XML below, I easily got the id and name for each row, but I'd like to bring the first value of field A into the top level for each row. With XPath something like this would work: 

//row/customfields/customfield[@fieldid="FA"]/customfieldvalues/customfieldvalue[1]

An example of my XML is like so:

<rows>
    <row>
        <id>1</id>
        <name>Item 1</name>
        <customfields>
            <customfield fieldid="FA">
                <customfieldname>Field A</customfieldname>
                <customfieldvalues>
                    <customfieldvalue>
                        Value 1     
                    </customfieldvalue>
                    <customfieldvalue>
                        Value 2
                    </customfieldvalue>
                </customfieldvalues>
            </customfield>
            <customfield fieldid="FB">
                <customfieldname>Field B</customfieldname>
                <customfieldvalues>
                </customfieldvalues>
            </customfield>
        </customfields>
    </row>
    <row>
        <id>2</id>
        <name>Item 2</name>
        <customfields>
            <customfield fieldid="FA">
                <customfieldname>Field A</customfieldname>
                <customfieldvalues>
                    <customfieldvalue>
                        Value 1     
                    </customfieldvalue>
                </customfieldvalues>
            </customfield>
            <customfield fieldid="FB">
                <customfieldname>Field B</customfieldname>
                <customfieldvalues>
                </customfieldvalues>
            </customfield>
        </customfields>
    </row>
    <row>
        <id>3</id>
        <name>Item 3</name>
        <customfields>
            <customfield fieldid="FA">
                <customfieldname>Field A</customfieldname>
                <customfieldvalues>
                </customfieldvalues>
            </customfield>
            <customfield fieldid="FB">
                <customfieldname>Field B</customfieldname>
                <customfieldvalues>
                </customfieldvalues>
            </customfield>
        </customfields>
    </row>
</rows>

Thank you

[max_totara]

Thank you. Somehow, the attribute filtering wasn't getting picked up, but the field name filtering did work.
I had issues where The Table.ExpandTableColumn was trying to be smart, if there was only one customfieldvalue element below, it would extract the text directly, if there was multiple customfieldvalue elements, then I'd get a table. This insconsistency lead to a lot of issues.
So I ended up loading the same XML into a separate query, just keeping the item's ID from the top level and using several layers of Table.ExpandTableColumn based on the code you supplied, I was able to get all of the customfieldvalues for each item. And a relationship between the 2 queries over id.

[rajendraongole1 · Accepted solution]

Hi @max_totara - To retrieve the first value of "Field A" from your XML structure and bring it to the top level for each row, you can use Power Query's native XML handling capabilities, which unfortunately don't directly support XQuery. 

alternatively, you can still achieve this by parsing the XML structure using functions like Xml.Tables, Table.ExpandTableColumn, and Table.TransformColumns.

In Power Query, you can load the XML file directly from a source, such as a file, or copy-paste it into the query editor.Use Xml.Tables to Convert XML to a Table: Power Query has a function called Xml.Tables that can parse the XML into a structured table.Navigate Through the Nested Tables: Once the XML is parsed, you'll need to drill down through the hierarchy to get the value of customfieldvalue for Field A. You can use Table.ExpandTableColumn to flatten the nested columns and bring the custom field value to the top level.

reference code attached FYI