extracting data

Highlighted
Six Stars

extracting data

hi everyone

i create a queue with a data from a table of db and im going to consume the queue and insert the data in another table ..

i use for this textractxml field it just read the rows and icant find any output

first i create a queue on my activemq with data from db table

second i consume it but it failed i cant understand what is the problem with the t extraxtxml fieled

 

Screenshot from 2018-12-27 18-16-48.pngScreenshot from 2018-12-27 18-22-09.png


Accepted Solutions
Employee

Re: how to extract data from xml and insert it on db table ?

Hi,

 

    In your input xml example from source, each client is coming as separate message.

 

<?xml version="1.0" encoding="UTF-8"?>
<root><clients/><client id="0"><cnom>baha</cnom><cprenom>dbeybia</cprenom><cage>22</cage></client></root>

If it has to come as loop with multiple customers, the XML would be as shown below.

 

<?xml version="1.0" encoding="UTF-8"?>
<root><clients>
<client id="0"><cnom>baha</cnom><cprenom>dbeybia</cprenom><cage>22</cage></client>
<client id="1"><cnom>jalel</cnom><cprenom>slimen</cprenom><cage>25</cage></client>
</clients></root>

As per your example, I have taken one client per XML (the first XML) for solution.  Please refer the output below.

image.png

 

 

In the example, the first two components is to convert the data to Document data type. Since I am not using queue, I have simulated it by creating a XML string using trowgenerator and converting it into Document type by tConvertType.

 

In your real time scenario, you can directly join tXMLMap to input message in Document format.

 

You will have to map the data in Document data type to target columns as shown below. Please note that id has to be created as an attribute and not as sub element.

 

image.png

 

 

Once you complete the mapping as shown above, you will get output as columns which can be transferred to your target DB (instead of tLogrow).

 

Hope, I have answered your query.

 

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

 

 

Employee

Re: how to extract data from xml and insert it on db table ?

Hi,

 

    In this case, could you please keep every column in output schema as String and then execute the flow?

 

    If you have to convert the datatype of any specific column to Integer, you can do it by tConverttype later in your job flow.

 

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: how to extract data from xml and insert it on db table ?

Hi,

 

   The first stage in Debugging is to verify whether you are getting the output back from queue in right format. To check that please add a tlogrow and verify the output.

 

  If its working fine, then the next stage is to verify the textractXMLfield. Could you please share a sample XML and the screenshot of your textractXMLfield for reference? I believe the mapping of the fields is not correct. 

 

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

Six Stars

Re: how to extract data from xml and insert it on db table ?

the output from tlog row when i consume the queue :

[statistics] connecting to socket on port 3391
[statistics] connected
Connecting to URL: tcp://localhost:61616
Consuming queue: ClientData1
We will wait for messages within: 1000 ms, and then we will shutdown
<?xml version="1.0" encoding="UTF-8"?>
<root><clients/><client id="0"><cnom>baha</cnom><cprenom>dbeybia</cprenom><cage>22</cage></client></root>
<?xml version="1.0" encoding="UTF-8"?>
<root><clients/><client id="1"><cnom>jalel</cnom><cprenom>slimen</cprenom><cage>25</cage></client></root>
<?xml version="1.0" encoding="UTF-8"?>
<root><clients/><client id="2"><cnom>wahbi</cnom><cprenom>khazri</cprenom><cage>36</cage></client></root>
<?xml version="1.0" encoding="UTF-8"?>
<root><clients/><client id="3"><cnom>mohamed</cnom><cprenom>ben ali</cprenom><cage>40</cage></client></root>
<?xml version="1.0" encoding="UTF-8"?>
<root><clients/><client id="4"><cnom>said</cnom><cprenom>bjeoui</cprenom><cage>65</cage></client></root>
<?xml version="1.0" encoding="UTF-8"?>
<root><clients/><client id="5"><cnom>hatem</cnom><cprenom>ben arfa</cprenom><cage>20</cage></client></root>
<?xml version="1.0" encoding="UTF-8"?>
<root><clients/><client id="6"><cnom>ali</cnom><cprenom>fff</cprenom><cage>22</cage></client></root>
<?xml version="1.0" encoding="UTF-8"?>
<root><clients/><client id="7"><cnom>mesba7</cnom><cprenom>jarbou3</cprenom><cage>90</cage></client></root>
<?xml version="1.0" encoding="UTF-8"?>
<root><clients/><client id="8"><cnom>chaima</cnom><cprenom>challouf</cprenom><cage>23</cage></client></root>
<?xml version="1.0" encoding="UTF-8"?>

<root><clients/><client id="9"><cnom>jamil</cnom><cprenom>benammar23</cprenom><cage>23</cage></client></root>
[statistics] disconnected

Screenshot from 2018-12-28 05-56-15.png

the XML

Screenshot from 2018-12-28 06-03-06.png

 

th textractfieled reference

Screenshot from 2018-12-28 06-05-24.png

 

my database table

Screenshot from 2018-12-28 06-06-53.png

 

 

Employee

Re: how to extract data from xml and insert it on db table ?

Hi,

 

    In your input xml example from source, each client is coming as separate message.

 

<?xml version="1.0" encoding="UTF-8"?>
<root><clients/><client id="0"><cnom>baha</cnom><cprenom>dbeybia</cprenom><cage>22</cage></client></root>

If it has to come as loop with multiple customers, the XML would be as shown below.

 

<?xml version="1.0" encoding="UTF-8"?>
<root><clients>
<client id="0"><cnom>baha</cnom><cprenom>dbeybia</cprenom><cage>22</cage></client>
<client id="1"><cnom>jalel</cnom><cprenom>slimen</cprenom><cage>25</cage></client>
</clients></root>

As per your example, I have taken one client per XML (the first XML) for solution.  Please refer the output below.

image.png

 

 

In the example, the first two components is to convert the data to Document data type. Since I am not using queue, I have simulated it by creating a XML string using trowgenerator and converting it into Document type by tConvertType.

 

In your real time scenario, you can directly join tXMLMap to input message in Document format.

 

You will have to map the data in Document data type to target columns as shown below. Please note that id has to be created as an attribute and not as sub element.

 

image.png

 

 

Once you complete the mapping as shown above, you will get output as columns which can be transferred to your target DB (instead of tLogrow).

 

Hope, I have answered your query.

 

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

 

 

Six Stars

Re: how to extract data from xml and insert it on db table ?

thank you sir i try it look what i get when i debug the job

the first component is  my queue with the document message

Screenshot from 2018-12-28 08-16-45.png

i map it into txmlmap

Screenshot from 2018-12-28 08-16-42.png

and iwant to test it on tlogrow but it shows me  Type mismatch: cannot convert from String to Integer

Screenshot from 2018-12-28 08-16-51.png

Employee

Re: how to extract data from xml and insert it on db table ?

Hi,

 

    In this case, could you please keep every column in output schema as String and then execute the flow?

 

    If you have to convert the datatype of any specific column to Integer, you can do it by tConverttype later in your job flow.

 

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

 

 

Six Stars

Re: how to extract data from xml and insert it on db table ?

its fine when i test it with tlogrow ( i just change the output to string) :

Screenshot from 2018-12-28 12-04-05.png

but if i out mydb table it shows this error :

Screenshot from 2018-12-28 12-04-15.pngScreenshot from 2018-12-28 12-14-01.png

Six Stars

Re: how to extract data from xml and insert it on db table ?

thank you sir i found the problem and i fix it it works thank you for ure time <3

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

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

Talend API Designer – Technical Overview

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

Watch Now

Getting Started with APIs

Find out how to get started with APIs

Read