Forum Discussion
XML Source - Getting child value with XQuery
Hi Anonymous - 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
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.