CSV like string inside XML field

Four Stars

CSV like string inside XML field

Hi guys,

I'm preparing ESB webservice to get data from client's CRM system and provide them for the website as nice JSON. I'm retrieving token from the API, creating nice XML request with this token, and retrieving information about the project. And here I have a little problem, because in nice XML answer I've got part which is more or less CSV format.

 

My Job looks like this:

Vertec_Job.PNG

response from the API in tLogRow3:

 

<Envelope>
  <Body>
    <QueryResponse>
      <Projekt>
        <objid>31329</objid>
        <code>XYZ-RELAUNCH</code>
        <projektleiter>John Doe</projektleiter>
         <phasen>31364,SPRINT1,01.11.2017,30.12.2017,Abgeschlossen#36896,SPRINT2,01.12.2017,31.01.2018,Abgeschlossen#36912,SPRINT3,01.01.2018,31.01.2018,Abgeschlossen</phasen>
      </Projekt>
    </QueryResponse>
  </Body>
</Envelope>

If I'll transmit it to the tResponse I can have nice XML or JSON response with it. But I don't like this , and # separated part. I would like to have multiple phases of the project where each phase could have proper document structure like:

<phase>
  <id>31364</id>
  <code>SPRINT1</code>
  <start>date1</start>
  <end>date2</end>
  <status>status</status>
<phase>

Is there a smart way to achieve it?

I was thinking about using phasen property as the second flow output from tXMLMap and then working on it - that's why there is second output there.

Then I'm having in tLogRow_4 this:

 

31364|SPRINT1|01.11.2017|30.12.2017|Abgeschlossen
36896|SPRINT2|01.12.2017|31.01.2018|Abgeschlossen
36912|SPRINT3|01.01.2018|31.01.2018|Abgeschlossen
36916|SPRINT4|01.02.2018|28.02.2018|Abgeschlossen
36920|SPRINT5|01.03.2018|31.03.2018|Abgeschlossen

So the structure is correct, but it looks like I can't connect these 2 outputs from tXMLMap_1 as 2 inputs for tXMLMap_2 because there are from one flow.

 

 

I think that I have two possibilities now but don't know if they are correct because I'm pretty new to Talend world.

1) save both outputs from tXMLMap_1 to HashOutput and then use it as two tHashInputs in subjob - don't know if it's possible?

2) use tReplace in front of tExtractXMLField to replace all , charts with tags - so something like changing xml before it will be processed in tExtractXMLField. Here the small catch is that I will need 2 loops probably. Right now here I'm receiving XML as text content, opening and closing tags looks like there are escaped.

{
    "project": {
        "projectCode": "XXX-RELAUNCH",
        "projectId": 31329,
        "projectLead": "John Doe",
        "projectPhases": "<phasen><phase>31364||SPRINT1||01.11.2017||30.12.2017||Abgeschlossen</phase><phase>36896||SPRINT2||01.12.2017||31.01.2018||Abgeschlossen</phase><phase>36912||SPRINT3||01.01.2018||31.01.2018||Abgeschlossen</phase><phase>36916||SPRINT4||01.02.2018||28.02.2018||Abgeschlossen</phase></phasen>"
    }
}

|| are there just temporary, didn't wanted to spend more time on formatting message there if the whole idea will not be working.


Any ideas what will be the best approach to deal with it?

 

Best,

Sz


Accepted Solutions
Employee

Re: CSV like string inside XML field

Hi,

 

    I could not see the tXMLmap2 in your diagram. But I am assuming that you are using it at later stage to process the data in same flow to different format.

 

     I personally like the tHashOutput way since it will hold the data in memory and can wipe the data after reading the hash using tHashInput. Neat, clean and easy to hold values for a web request where speed is the essence.

 

     Only thing is that you need to make sue that you are clearing the hash after second reading.

 

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

 


All Replies
Employee

Re: CSV like string inside XML field

Hi,

 

    I could not see the tXMLmap2 in your diagram. But I am assuming that you are using it at later stage to process the data in same flow to different format.

 

     I personally like the tHashOutput way since it will hold the data in memory and can wipe the data after reading the hash using tHashInput. Neat, clean and easy to hold values for a web request where speed is the essence.

 

     Only thing is that you need to make sue that you are clearing the hash after second reading.

 

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

 

Highlighted
Employee

Re: CSV like string inside XML field

@sgregor,

 

     If you are happy with the details given, could you please mark the topic as resolved?

 

     It will help other Talend community members also :-)

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

An API-First Approach to Modernizing Applications

Learn how to use an API-First Approach to Modernize your Applications

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now