Search code examples
oracle-databasedata-migrationtalend

How to split a column which has data in XML form to different rows of new Database as KEY VALUE in TALEND

In old DB i have a data in one column as 

<ADDRESS>
<CITY>ABC</CITY>
<STATE>PQR</SERVICE>
</ADDRESS>

In my new DB i want this data to be stored in KEY VALUE fashion like:

USER_ID            KEY                VALUE
1                  CITY                ABC
1                  STATE               PQR

Someone please help me how to migrate this kind of data using TALEND tool.

Solution

  • Design job like below.

    tOracleInput---tExtractXMLFiled---output.

    1. tOracleInput component you can select XML column and make datatype as String.
    2. tExtractXmlFiled component pass this XML column as " XML Filed" and set the Loop xpath Expression as "/ADDRESS"
    3. Add new two Columns in output Schema of tExtractXmlFiled for city & STATE
    4. Set XPath Query in Mapping for city "/ADDRESS/CITY" and for STATE "/ADDRESS/STATE"
    5. Now you have both the values in output.

    See the image for more details. enter image description here

    as I explain in your previous post you can follow the same approach for making Key value pair.
    how-to-split-one-row-in-different-rows-in-talend

    Or you can use tUnpivot component as you did here.

    As you said source data has Special character then use below expression to replace it.

    Steps: after oracle input add tMap and use this code for replacement of special symbol

    row24.XMLField.replaceAll("&", "<![CDATA["+"&"+"]]>")

    once that is done execute the job and see the result it should work.