Grouping XML Data using Altova and Logic Apps

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Altova MapForce is an excellent tool for mapping and transforming data between different formats. The tool allows business users and domain experts to manipulate data without requiring coding skills.

I had a scenario where an invoice contained thousands of lines. To make it easier to parse, we would need to consolidate on product code to reduce the length of the invoice.

In this example, I am using Altova MapForce to generate an XLST 2.0 mapping function and deploying this into an Azure logic app for use in an integration service.

As I was working on a long and advanced real-life invoice, this would not be proper to show here. This is instead an example XML:

				
					<Invoice>
    <Products>
        <Product>
            <Code>45585</Code>
            <Type>A</Type>
            <Description>An item of type A.</Description>
            <Price>75.00</Price>
            <ReorderLimit>100</ReorderLimit>
        </Product>
        <Product>
            <Code>45585</Code>
            <Type>B</Type>
            <Description>An item of type B.</Description>
            <Price>25.00</Price>
            <ReorderLimit>70</ReorderLimit>
        </Product>
        <Product>
            <Code>45587</Code>
            <Type>A</Type>
            <Description>An item of type A.</Description>
            <Price>65.00</Price>
            <ReorderLimit>100</ReorderLimit>
        </Product>
        <Product>
            <Code>45587</Code>
            <Type>A</Type>
            <Description>An item of type A.</Description>
            <Price>45.00</Price>
            <ReorderLimit>100</ReorderLimit>
        </Product>
        <Product>
            <Code>45585</Code>
            <Type>B</Type>
            <Description>An item of type B.</Description>
            <Price>35.00</Price>
            <ReorderLimit>80</ReorderLimit>
        </Product>
    </Products>
</Invoice>
				
			
I will open MapForce and drag-drop the XML into the workspace. MapForce will ask to automatically create an XSD, which I will accept. I also drag and drop that onto the workspace. We can now aggregate these values based on a set key by adding the “group-by” function, then adding the “Code” as the key and connect “Product” to “Product” as:

This is fine, however I would also like to aggregate on a compound key of both “Code” and “Type”. So let’s add a concatenate statement and make that the key instead. I also want to get the sum price and the average reorder limit, so adding Sum and Average functions. 

This mapping will produce the following result.

				
					<Invoice>
	<Products>
		<Product>
			<Code>45585</Code>
			<Type>A</Type>
			<Description>An item of type A.</Description>
			<Price>75</Price>
			<ReorderLimit>100</ReorderLimit>
		</Product>
		<Product>
			<Code>45585</Code>
			<Type>B</Type>
			<Description>An item of type B.</Description>
			<Price>60</Price>
			<ReorderLimit>75</ReorderLimit>
		</Product>
		<Product>
			<Code>45587</Code>
			<Type>A</Type>
			<Description>An item of type A.</Description>
			<Price>110</Price>
			<ReorderLimit>100</ReorderLimit>
		</Product>
	</Products>
</Invoice>
				
			
Now, we can convert the mapping to XSLT 2.0 by generating the code. Use “Menu/File/Generate code in…/XSLT 2.0” and save it to disk, by selecting Desktop and press save.

ADDING THE XSLT 2.0 MAPPING IN A LOGIC APP

Following the previous XLST logic sample, we can now create a logic flow.

First, create a new Integration Service Environment in your Azure tenant. After it has been created, go to the resource and

  • click “Maps” from the overview page
  • click “Add”
  • set the type to “XSLT 2.0”
  • browse for the XSLT file that was saved earlier
  • the name is set automatically
  • click OK

Then, create a new Logic App. This will cause the logic app designer to open, so set the activation to “When a HTTP message is received” and then save the workflow and close the editor (top right X or click the logic app name in the breadcrumbs).

Now, from the logic app blade, select “Workflow Settings” and link the new integration service.

Go back to the workflow editor (Overview, then Edit) and:

  • create a new step using the component “Transform XML” under the “Built-in” section
  • in “content” add the Body of the incoming HTTP message. If you can’t see it then press “see more” in the dialog
  • in “map”, select the mapping you uploaded earlier
  • add a new step using the component “Response” under the “Built-in” section
  • add the output from the XSLT transformation into the Body field
  • save the workflow
  • click “When a HTTP request is received” and copy the HTTP POST URL

The workflow should look like this.

Now, test the workflow

  • from the overview screen on the logic app, press 
  • install Postman
  • press “New” in Postman and create a new request
  • Set the method to POST and paste the HTTP POST URL into URL
  • Under the Body tab, set format to Raw, use the menu to the right of these boxes to select XML then paste the invoice.xml file content into the window

Then click SEND. You should get a response now from the logic app, containing the transformed XML. The workflow run history will show the execution results if you need to troubleshoot or inspect the transformation.

Leave a Reply

Your email address will not be published.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.