Need help mapping Xml output

[stephenJames]

Hello,

My end goal is to get this XML into an Excel file.

I have watched a few YouTube videos but none seem to explain mapping out my particular xml output. 

My xml is as follows:

  <Row>
    <Item>Date Created</Item>
    <Item>Date Last Updated</Item>
    <Item>SR Number</Item>
    <Item>Name</Item>
    <Item>Contact</Item>
    <Item>Assigned</Item>
    <Item>Module</Item>
    <Item>Category</Item>
    <Item>Activity</Item>
    <Item>Status</Item>
    <Item>Issue Description</Item>
  </Row>

My xml file has multiple rows and the example above represents the column headers. 

Each row after this one has the row data for each column. 

I guess first off i am trying to isolate the column headers and output them to an excel worksheet. 

Next i need to iterate over each of the remaining rows and add the row data to the same excel sheet. 

From the YouTube videos i have watched they are able to get the text data for each row item cause the row items have identifiable names, mine don't and all say "Item"

I thought i would be able to grab each one with something like

item(outputs('GetEachRowData'))[0]

but this doesn't work. I believe i should be able to get the data into an excel table after i can figure out this hurdle. 

[grantjenkins]

@stephenJames Are you able to show an example of your XML including the headers and at least one row of data. And any other outer elements that wrap the rows.

Also, will the rows always contain the same headers and in the same order?

It's definitely doable - just want to make sure I'm working with the same data that you have.

[grantjenkins]

Hopefully this will get what you're looking for. I've assumed you have a root node called Report and a collection of Row elements containing Item elements. Also assumed you always have the same number of headers.

See full flow below. I'll go into each of the actions.

XML is a Compose that contains your XML data. For this example, I've added a couple of rows and wrapped the rows within a Report element.

<Report>
    <Row>
        <Item>Date Created</Item>
        <Item>Date Last Updated</Item>
        <Item>SR Number</Item>
        <Item>Name</Item>
        <Item>Contact</Item>
        <Item>Assigned</Item>
        <Item>Module</Item>
        <Item>Category</Item>
        <Item>Activity</Item>
        <Item>Status</Item>
        <Item>Issue Description</Item>
    </Row>
    <Row>
        <Item>2021-12-11</Item>
        <Item>2022-12-11</Item>
        <Item>SR-1</Item>
        <Item>Name 1</Item>
        <Item>Contact 1</Item>
        <Item>Assigned 1</Item>
        <Item>Module 1</Item>
        <Item>Category 1</Item>
        <Item>Activity 1</Item>
        <Item>Status 1</Item>
        <Item>Issue Description 1</Item>
    </Row>
    <Row>
        <Item>2021-12-11</Item>
        <Item>2022-08-11</Item>
        <Item>SR-2</Item>
        <Item>Name 2</Item>
        <Item>Contact 2</Item>
        <Item>Assigned 2</Item>
        <Item>Module 2</Item>
        <Item>Category 2</Item>
        <Item>Activity 2</Item>
        <Item>Status 2</Item>
        <Item>Issue Description 2</Item>
    </Row>
</Report>

Headers is a Compose that extracts out an array containing all the headers. It uses the following XPath expression.

xpath(xml(outputs('XML')), '//Report/Row[1]/*/text()')

This will give us the following output:

Select extracts out all the rows (excluding the headers). The input is using an XPath expression, then chunking the results in batches of 11 (11 items in each row) and skips the first chunk (our headers). The expressions used is below:

skip(chunk(xpath(xml(outputs('XML')), '//Report/Row/*/text()'), 11), 1)

And uses the following expressions to Map the headers and values.

//Header
outputs('Headers')[0]

//Value
item()?[0]

//You would just need to increase the index for each of the 11 items.

If you Switch Map to text mode (as highlighted above) then you can just copy/paste the below code, then switch back to key value mode.

{
  "@{outputs('Headers')[0]}": @{item()?[0]},
  "@{outputs('Headers')[1]}": @{item()?[1]},
  "@{outputs('Headers')[2]}": @{item()?[2]},
  "@{outputs('Headers')[3]}": @{item()?[3]},
  "@{outputs('Headers')[4]}": @{item()?[4]},
  "@{outputs('Headers')[5]}": @{item()?[5]},
  "@{outputs('Headers')[6]}": @{item()?[6]},
  "@{outputs('Headers')[7]}": @{item()?[7]},
  "@{outputs('Headers')[8]}": @{item()?[8]},
  "@{outputs('Headers')[9]}": @{item()?[9]},
  "@{outputs('Headers')[10]}": @{item()?[10]}
}

If you ran the flow now, you would get the following output:

[
  {
    "Date Created": "2021-12-11",
    "Date Last Updated": "2022-12-11",
    "SR Number": "SR-1",
    "Name": "Name 1",
    "Contact": "Contact 1",
    "Assigned": "Assigned 1",
    "Module": "Module 1",
    "Category": "Category 1",
    "Activity": "Activity 1",
    "Status": "Status 1",
    "Issue Description": "Issue Description 1"
  },
  {
    "Date Created": "2021-12-11",
    "Date Last Updated": "2022-08-11",
    "SR Number": "SR-2",
    "Name": "Name 2",
    "Contact": "Contact 2",
    "Assigned": "Assigned 2",
    "Module": "Module 2",
    "Category": "Category 2",
    "Activity": "Activity 2",
    "Status": "Status 2",
    "Issue Description": "Issue Description 2"
  }
]

Hopefully that will give you what you need to save your data to Excel.

If you were creating a new Excel Table and wanted a comma separated list of headers, you can use a join expression on the Headers action.

join(outputs('Headers'), ',')

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[stephenJames]

@grantjenkins Thanks for your response. 

I am getting an error on the Headers compose

"Unable to process template language expressions in action 'Headers' inputs at line '0' and column '0': 'The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'Data at the root level is invalid. Line 1, position 1.'. Please see https://aka.ms/logicexpressions#xml for usage details.'."

I think it might have something to do with the xml output. It appears my xml document has some extra fields.

<?xml version="1.0" encoding="utf-8"?>
<Report>
    <Field>Title</Field>
    <Row>
        <Item>Date Created</Item>
        <Item>Date Last Updated</Item>
        <Item>SR Number</Item>
        <Item>Name</Item>
        <Item>Contact</Item>
        <Item>Assigned</Item>
        <Item>Module</Item>
        <Item>Category</Item>
        <Item>Activity</Item>
        <Item>Status</Item>
        <Item>Issue Description</Item>
    </Row>
    <Row>
        <Item>2021-12-11</Item>
        <Item>2022-12-11</Item>
        <Item>SR-1</Item>
        <Item>Name 1</Item>
        <Item>Contact 1</Item>
        <Item>Assigned 1</Item>
        <Item>Module 1</Item>
        <Item>Category 1</Item>
        <Item>Activity 1</Item>
        <Item>Status 1</Item>
        <Item>Issue Description 1</Item>
    </Row>
    <Row>
        <Item>2021-12-11</Item>
        <Item>2022-08-11</Item>
        <Item>SR-2</Item>
        <Item>Name 2</Item>
        <Item>Contact 2</Item>
        <Item>Assigned 2</Item>
        <Item>Module 2</Item>
        <Item>Category 2</Item>
        <Item>Activity 2</Item>
        <Item>Status 2</Item>
        <Item>Issue Description 2</Item>
    </Row>
    <Field>Record Count: 1</Field>
</Report>

There is the <?xml version="1.0" encoding="utf-8"?> at the top of the document as well as two "Field" rows. I am not interested in these Field rows for the extraction. 

How would i go about adjusting the expression to account for these (If thats what the problem is)

[grantjenkins]

The extra fields shouldn’t cause any issues. Just off to sleep now (midnight for me and 6AM meeting 🙂). Will have a look for you tomorrow.

[stephenJames]

@grantjenkins so i have managed to figure out the issue. The data source xml file i am using had a character at the beginning of the file that wasnt recognized by xpath. 

So i had to run a replace to remove the character then everything worked great.

replace(outputs('decode'),uriComponentToString('%EF%BB%BF'), '')