Reading CSV(array data in "") and creating an XML

[jdheerajr@gmail.com]

In some cases I have, the CSV has comma separated values in one column e.g. "ColumnAValue1","ColumnBValue1,ColumnBValue2,ColumnBValue3","ColumnCValue1,ColumnCValue2" is one row of the CSV file.
This has to be converted to XML as
<ColumnA>ColumnAValue1</ColumnA>
<ColumnB>ColumnBValue1</ColumnB>
<ColumnB>ColumnBValue2</ColumnB>
<ColumnB>ColumnBValue3</ColumnB>
<ColumnC>ColumnCValue1</ColumnC>
<ColumnC>ColumnCValue2</ColumnC>

I want my sequential view data to look as:
"ColumnAValue1","ColumnBValue1","ColumnBValue2","ColumnBValue3","ColumnCValue1","ColumnCValue2"
This kind of array "1","3","2" might vary to any in future.

How to handle this scenario within datastage.

[eostic]

This one looks like it has a variety of challenges.

a) the need to get the multiple columns into multiple rows....the B's as a set and the C's as a set.....independent paths doing pivots will be needed for that.

b) the need to treat them as independent repeating nodes of a multi-path xml document. Look thru the forum --- find the "XML Best Practices" document links. Absorb that document. That will be part of your "independent paths" up above.

c) you may need a "containment" element for the "B"s and for the "C"s...though that is a minor issue that you'll address when you work on creating your independent xml for the B's and then for the C's.

...this is much easier in 8.5 with the new XML Stage.

Ernie