Forum Discussion
@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>
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