CSV to XML with repetitive xml nodes

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)

Hi @deepak.shaw

Could you please provide the elements’ structure of the XML file, so that we may be able to gain better insight as to which transformations and actions should be undertaken to resolve your particular use-case.

Thanks,
Dimitri

1 Like

I don’t believe your XML was attached correctly - please try again

1 Like
<?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>

I have provided the xml sample output

Hi Experts @robin @dimitri.hristovski, Kindly suggest a solution thanks

Hi @deepak.shaw ,

I have attached a sample pipeline and sample CSV file that I am using for processing.
In the XML Generator snap, if you click the button Edit XML, you can see some logic in there.

Please have a look and let me know if it helps you.

Regards,
Marjan
csv sample.txt (112 Bytes)
CSV_TO_XML_2022_05_18.slp (6.7 KB)

2 Likes

I just want to be clear - you want to drop the values for ProduceCode and ProductName (e.g. A001 and DellLaptop respectively) from the eventual XML, correct? The only values you want to keep are Price and Country, yes?

@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):

image

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

image

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:

image

[{
	"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:

image

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>
2 Likes