Parsing nested XML into single row

I have a source XML file that I’d like to be able to parse into a single row of data for export as csv/delimited file.

Right now when I import the file, it’s creating multiple rows with missing data in most of the columns. A snippet of the XML is below.

<CC_TRANSMISSION CATALOG_ID=“102975” NUMBER_OF_ORDERS=“1”>
<CC_ORDER AMAZON_NO="" INVOICE_NO=“7717763” IP_ADDRESS=“208.91.243.223” MAYBEBOGUS=“true” ORDER_NO=“6943603”>
<PAYMENT_METHOD>
<CREDIT_CARD>
<CC_TYPE>Visa</CC_TYPE>
<CC_NUMBER/>
<CC_EXPIRATION/>
<CC_NAMEONCARD>QA AUTOMATION MOR</CC_NAMEONCARD>
<CC_SECURITY_NUMBER/>
<CC_ISSUING_BANK/>
<CC_ISSUE_NUMBER/>
</CREDIT_CARD>
</PAYMENT_METHOD>
<BILLING_LABEL>

<CU_CUSTOMER_ID/>
<CU_FIRST_NAME>QA AUTOMATION</CU_FIRST_NAME>
<CU_LAST_NAME>MOR TEST</CU_LAST_NAME>
<CU_COMPANY/>
<CU_PHONE>2223334444</CU_PHONE>
<CU_EMAIL>TwoMORProducts2@yopmail.com</CU_EMAIL>
<CU_SHOPPER_ID>49392018</CU_SHOPPER_ID>
<CU_SHOPPER_FLAGS/>
<CU_NAME_PREFIX/>
<CU_DATE_OF_BIRTH/>


<AD_ADDRESS1/>
<AD_CITY/>
<AD_STATE/>
<AD_COUNTRY>United States</AD_COUNTRY>
<AD_PROVINCE/>
<AD_ZIP/>
<AD_FLAT/>
<AD_ADDRESS2/>
<AD_COUNTRY_CODE/>
<AD_COMPANY/>

</BILLING_LABEL>
</CC_ORDER>
</CC_TRANSMISSION>

The resulting import:

Is there a way to treat the hierarchy of the XML in such a way that can result in all of the data rolling up under the top level as a single row of data?

Hi Jason and welcome to the Community!

In a simple case, using the “Fill down” action should do the trick. However, if the structure of XML is not consistent, a more complicated method can be used. In the example below, I provided 2 methods how to flatten the XML you provided.

flatten-xml.morph (6.7 KB)
sample.xml (958 Bytes)

1 Like

Hi Jason,

i think it’s the same problem i also had several times.

regards ,

Adrian

Problem at solution
above (flatten-xml) is if you have more than one occurence (1:n, Loops …) in der XML. In this case only the first occurrence is taken. Would be great if Easymorph could work on XSDs

with best regards,

Adrian

@dgudkov - thank you! This is perfect and worked great when applied to the full XML file/format we’re working from. Separately, we’ve learned that the originating process for this file will potentially produce multiple files (each XML file representing a customer order), that would be delivered to us in a consistent directory location. If using the scheduler to process, is the best option to use the “List Files” action and then have it process with some conditions (specific file name, or when list is not empty…) to churn through the source files and process each ?

The idiomatic way to process multiple uniform files in EasyMorph is to use iterations (loops). Check out our tutorial on iterations here: EasyMorph | Iterations

Alternatively, you can use the “List files” action to produce a list of files in a specific location, then filter it (if necessary) using a filtering action in EasyMorph, then use the “Import from XML” action in the “Load multiple files” mode. The mode is described in this tutorial article (see the Advanced topics): EasyMorph | Loading files

If you choose to process multiple files at once, instead of iterations, you may need to adjust the logic in the example that I posted above because they are designed to work for one transmission (order). You may need to add grouping by file name so that operations are performed inside groups.