CSV to XML with repetitive xml nodes

[deepak_shaw]

Hi,
I’m trying to create a pipeline which suppose to create an xml file out of a CSV file.

CSV file looks like -
|ProductCode|ProductName|Price|Country|
|A001|DellLaptop|$400|US|
|A001|DellLaptop|$500|AU|
|A001|DellLaptop|$550|EU|

XML output should be -

<?xml version="1.0" encoding="UTF-8"?> US $400 AU $500 EU $550 [XML file attached as its not coming well in the question well..] Thanks for your answer, Deepak Shaw [Product.zip|attachment](upload://7NZiA8X09vabLf1eK3iLKeLDU5f.zip) (203 Bytes)

[robin]

deepak.shaw:

<?xml version='1.0' encoding='UTF-8'?>
<DocumentRoot>
    <Document>
        <Metadata>
            <asMap>
                <global>
                    <doc_id>ae803607-d70c-11ec-9afc-6bf587c0d9c9</doc_id>
                </global>
            </asMap>
            <id>ae803607-d70c-11ec-9afc-6bf587c0d9c9</id>
        </Metadata>

The Snap has a DocumentRoot value in the “Root Element” setting

[robin]

@marjan.karafiloski has provided a solution that leverages the Expression Language and the XML Generator and it results in the desired XML returned as a String in the output document. Thank you Marjan!

An alternative way to doing this is to use the Gate Snap with an XML Formatter.

But first…

When going from CSV via JSON to XML, it’s often useful to first experiment with JSON Generator + XML Formatter to figure out what input JSON structure equates to the desired XML.

In this case, to get XML that looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<ProductCode>
   <ProductName>
      <CostLocation>
         <country>US</country>
         <Cost>$400</Cost>
      </CostLocation>
      <CostLocation>
         <country>AU</country>
         <Cost>$500</Cost>
      </CostLocation>
      <CostLocation>
         <country>EU</country>
         <Cost>$550</Cost>
      </CostLocation>
   </ProductName>
</ProductCode>

you need JSON that looks like this:

[
    {
        "ProductCode": {
            "ProductName": {
                "CostLocation": [
                    {
                        "country": "US",
                        "Cost": "$400"
                    },
                    {
                        "country": "AU",
                        "Cost": "$500"
                    },
                    {
                        "country": "EU",
                        "Cost": "$550"
                    }
                ]
            }
        }
    }
]

So your goal should be to gather your stream of multiple documents in a way that allows you get to that single JSON Document above.

Breakdown:

The first Snap is a Constant to simulate the original CSV (pipe-delimited and pipe-enclosed):

Then a CSV Parser to convert it to Document-format and to use the pipe character as a delimiter:

Now a Mapper to drop all the values we don’t care about (and rename the fields we do care about):

Next a Gate Snap to gather the 3 documents into a single document:

[{
	"input0": [{
		"country": "US",
		"Cost": "$400"
	}, {
		"country": "AU",
		"Cost": "$500"
	}, {
		"country": "EU",
		"Cost": "$550"
	}]
}]

Now another Mapper to structure the JSON so it can be converted to the XML we want:

Finally a standard XML Formatter:

And then you have your desired XML in a binary output format, ready to be written to a File etc

<?xml version="1.0" encoding="UTF-8"?>
<ProductCode>
   <ProductName>
      <CostLocation>
         <country>US</country>
         <Cost>$400</Cost>
      </CostLocation>
      <CostLocation>
         <country>AU</country>
         <Cost>$500</Cost>
      </CostLocation>
      <CostLocation>
         <country>EU</country>
         <Cost>$550</Cost>
      </CostLocation>
   </ProductName>
</ProductCode>

[deepak_shaw]

Hi Robin,
Great solution and almost no coding involved.
Can you please help me know to introduce the namespaces into xml, the output looks like:

<?xml version="1.0" encoding="UTF-8"?>
<ProductCode xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"
    xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"
    xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2">
	<cbc:ProductName>
		<cac:CostLocation>
			<cbc:country>US</cbc:country>
			<cbc:Cost>$400</cbc:Cost>
		</cac:CostLocation>
		<cac:CostLocation>
			<cbc:country>AU</cbc:country>
			<cbc:Cost>$500</cbc:Cost>
		</cac:CostLocation>
		<cac:CostLocation>
			<cbc:country>EU</cbc:country>
			<cbc:Cost>$550</cbc:Cost>
		</cac:CostLocation>
	</cbc:ProductName>
</cbc:ProductCode>

Thanks,
Deepak Shaw

[robin]

deepak.shaw:

Can you please help me know to introduce the namespaces into xml

You’ll use our JSON-to-XML conventions, which are basically:

{
    "root": {
        "elementName": {
            "@attributeName": "attributeValue",
            "$": "elementValue"
        },
        "repeatedElement": [
            {
                "key": {
                    "@attr": "attr1",
                    "$": "key1"
                },
                "value": "value1"
            },
            {
                "key": {
                    "@attr": "attr2",
                    "$": "key2"
                },
                "value": "value2"
            }
        ]
    }
}

That input Document into the XML Formatter will generate the following XML:

<?xml version="1.0" encoding="UTF-8"?>
<root>
   <elementName attributeName="attributeValue">elementValue</elementName>
   <repeatedElement>
      <key attr="attr1">key1</key>
      <value>value1</value>
   </repeatedElement>
   <repeatedElement>
      <key attr="attr2">key2</key>
      <value>value2</value>
   </repeatedElement>
</root>

So, for the sample I provided above, the following should give you enough information to get to your final desired XML:

[
    {
        "ProductCode": {
            "@xmlns:cac": "urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2",
            "@xmlns:cbc": "urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2",
            "@xmlns": "urn:oasis:names:specification:ubl:schema:xsd:Invoice-2",
            "cbc:ProductName": {
                "cac:CostLocation": [
                    {
                        "cbc:country": "US",
                        "cbc:Cost": "$400"
                    },
                    {
                        "cbc:country": "AU",
                        "cbc:Cost": "$500"
                    },
                    {
                        "cbc:country": "EU",
                        "cbc:Cost": "$550"
                    }
                ]
            }
        }
    }
]

which results in:

<?xml version="1.0" encoding="UTF-8"?>
<ProductCode xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2">
   <cbc:ProductName>
      <cac:CostLocation>
         <cbc:country>US</cbc:country>
         <cbc:Cost>$400</cbc:Cost>
      </cac:CostLocation>
      <cac:CostLocation>
         <cbc:country>AU</cbc:country>
         <cbc:Cost>$500</cbc:Cost>
      </cac:CostLocation>
      <cac:CostLocation>
         <cbc:country>EU</cbc:country>
         <cbc:Cost>$550</cbc:Cost>
      </cac:CostLocation>
   </cbc:ProductName>
</ProductCode>

Go from there to finish the rest of the XML you want.

[deepak_shaw]

HI @robin ,
Thanks for the reply…
The steps you have suggested is (as I understand):
JSON Generator >> XML Formatter >> File Writer

If Yes, I still don’t understand how to introduce the namespace…

Related to JSON-to-XML conventions, are you referring to this URL:Convert json to xml

If possible can have a sample pipeline?
Thanks,
Deepak.