Extracting data from multiple XML files

AlexChen, thanks for your reply.

The context is extracting data from SSRS RDL files - which are XML files. Specifically, I want to retrieve the query in each file. So I've set up a top-level query that retrieves the directory listing, and filters it to *.rdl. Then I open each file and begin parsing it.

That works great - as long as the query is in the RDL file itself. But if it uses a Shared Dataset, the nodes in the RDL file are different, and I need to retrieve the query from the linked RSD file.

So here's the query that processes a file. (Note - the real query will pass in the path and file name - I have that working, but I'm using this for testing.)

Query3:

let
Source = Xml.Tables(File.Contents("C:\Users\sam.trexler\Documents\Reporting\NY Sample Reports\State\Amanda C's Folder\All DEC 2014 MY 2001.rdl")),
#"Removed Columns" = Table.RemoveColumns(Source,{"AutoRefresh"}),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Columns",{"ReportSections", "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner", "ReportParameters"}),
#"Process RDL" = Query4(#"Removed Columns1")
in
#"Process RDL"

And here's the start of the function to process the file:

Query4:

(DataSetTable as list) =>
let
#"Expanded DataSets" = Table.ExpandTableColumn(DataSetTable, "DataSets", {"DataSet"}, {"DataSets.DataSet"}),
#"Expanded DataSets.DataSet" = Table.ExpandTableColumn(#"Expanded DataSets", "DataSets.DataSet", {"Attribute:Name", "Query"}, {"DataSets.DataSet.Attribute:Name", "DataSets.DataSet.Query"}),
#"Expanded DataSets.DataSet.Query" = Table.ExpandTableColumn(#"Expanded DataSets.DataSet", "DataSets.DataSet.Query", {"CommandText"}, {"DataSets.DataSet.Query.CommandText"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded DataSets.DataSet.Query","DataSets.DataSet.Query.CommandText",Splitter.SplitTextByDelimiter("SELECT", QuoteStyle.Csv),{"DataSets.DataSet.Query.CommandText.1", "DataSets.DataSet.Query.CommandText.2"}),
#"Removed Columns2" = Table.RemoveColumns(#"Split Column by Delimiter",{"DataSets.DataSet.Query.CommandText.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"DataSets.DataSet.Query.CommandText.2", "ColumnList"}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns","ColumnList",Splitter.SplitTextByDelimiter("FROM", QuoteStyle.Csv),{"ColumnList.1", "ColumnList.2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Split Column by Delimiter1",{{"ColumnList.1", "ColumnList"}, {"ColumnList.2", "TableList"}})
in
#"Renamed Columns1"

Now here's the problem: At step "Removed Columns1" Query3 looks like this:

I've found that if I elminate the DataSources column, I can pass the DataSets table just fine, and process it. But I'd really like to pass the whole row. And here's the error I get:

I haven't been able to figure out the syntax to pass a record of tables - neither "list" nor "record" nor "table" work in the definition of Query4, regardless of the syntax I use in the call to the function. Is there a way to do this that I haven't tried?

Once I pass the whole record in and perform some processing on it, I'll need to check to see if the "DataSet" node exists, or the "SharedDataSet" node. Based on an "if" statement, I'll need to call one of two other functions to do the remaining processing. I haven't written an "if" yet, but I think I can handle that.

Any help is greatly appreciated. Thanks,

Sam