Mapping a document with parent-child relationships to CSV

In the destination map, we need to access elements from two different tiers in the source XML hierarchy - transaction level elements like the name and order number come from the Order level, and line level elements like the item name and cost come from the Order Line level.

For smaller source documents, the simplest way to create such a mapping it to begin by creating your Foreach mapping to the destination at the line level that you are mapping, to, creating a Foreach from the Orders/Items element in this case to the OrderLines in the destination:

 A frequent case that come up in CData Arc is a need to map a more complex data structure (such as an order) into a flat structure, such as a CSV. For this example, we’re going to use a source XML with two orders:

<Items>
    <Orders>
        <CustomerName>Teddy Blasingame</CustomerName>
        <PONumber>PO042619</PONumber>
        <Items>
            <Name>Number Puzzle</Name>
            <Cost>14.99</Cost>
        </Items>
        <Items>
            <Name>Letter Puzzle</Name>
            <Cost>14.99</Cost>
        </Items>
    </Orders>
    <Orders>
        <CustomerName>Tommy Blasingame</CustomerName>
        <PONumber>PO101821</PONumber>
        <Items>
            <Name>Plush Fox</Name>
            <Cost>9.99</Cost>
        </Items>
        <Items>
            <Name>Bottle</Name>
            <Cost>9.99</Cost>
        </Items>
    </Orders>
</Items>

And map this to a simple CSV structure: 

If you upload the attached XML and CSV files as the source templates for the XML Map connector and CSV connector (see https://cdn.cdata.com/help/AZH/mft/XML-Map-Template-Files.html for more details on template files), you will see that the mapping relationship starts out like this:   

In the destination map, we need to access elements from two different tiers in the source XML hierarchy - transaction level elements like the name and order number come from the Order level, and line level elements like the item name and cost come from the Order Line level.

For smaller source documents, the simplest way to create such a mapping it to begin by creating your Foreach mapping to the destination at the line level that you are mapping, to, creating a Foreach from the Orders/Items element in this case to the OrderLines in the destination:

As you can see, it is easy to map elements that exist on the line level in the source to the destination, but if you want to map elements from the parent element, you can simply drag and drop them from the same loop to the destination:

Note that the XPaths to these elements are relative XPaths - they begin with the sequence .. just like file paths that reference a parent element on the file system.

This type of mapping is fairly simple to create, and these relative paths can be more complex than simply one parent higher. 

For smaller source files this is an effective way to perform the mapping (what this entails can depend on your available system resources, but to pull am arbitrary distinction out of the air, let’s say 50 KB), but there is one drawback to this approach - the use of a relative XPath in an XML Map requires that the entire XML document model be loaded in memory so that values in a relative path in the tree can be resolved. 

In a separate discussion, I will discuss approaches to address this when dealing with larger input files (for messages that are larger, such as inventory lists and healthcare reporting).

Attached to this discussion is the arcflow that is generated above, and the CSV file that served as a sample for input (the source XML is embedded in the arcflow). To see this flow in action, simply click on the More->Create Test Files in the Input tab of the OrderToCSVSimple connector.