Seven Stars

How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

Hi All,

I would Like to know how to read JSON/XML data from API and Load into MySQL table.

Usually, we will give some XML or JSON data in HTTP body, Is there any ways to call table in Http body in tRest component inTalend.

 

I look forward to hearing from you.

 

Thanks in advance.

 

  • Data Integration
1 ACCEPTED SOLUTION

Accepted Solutions
Community Manager

Re: How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

Is a URL available for your API calling? If so, try to use tRest or tRestClient component to call your API, tRest component will returns a XML/Json string, and then, use a tExtractXMLField or tExtractJsonField to extract data from the return string, insert the data to target table, a simple job design looks like:
tRest--main--tExtractXMLField--main--tMysqlOutput.

Please read the documentation of these two components and learn them.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
7 REPLIES
Seven Stars

Re: How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

I just share my idea here someone please help me out.

Scenario 1:

Firstly through trest component extracting XML data and saving those data into one table.say that table name as (XMLDATA)

Later from XMLDATA table have to get specific fields and load those into target table (TaxResult).

 

Scenario 2: 

From trest component is this possible to call Table(XMLDATA)  and Load specific fields into target table (TaxResult).

 

Please, someone, tell me the possibilities.

 

It would be grateful if you share with example screenshots.

 

Thanks In Advance.

Seven Stars

Re: How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

For Scenario 1, I think it's pretty straight forward,

You just have to configure the tRestComponent to retrieve the XML data and then connect it to tExtractXMLField component.

Finally the output of the tExtractXMLField goes to the tMySQLOutput Component.

extractXML.PNG

I need more information for Scenario 2. I don't understand why you would need to use tRest Component to retrieve data from a table. I am guessing you don't really need it if what I have mentioned works.

 

Let me know what you think.

Community Manager

Re: How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

Is a URL available for your API calling? If so, try to use tRest or tRestClient component to call your API, tRest component will returns a XML/Json string, and then, use a tExtractXMLField or tExtractJsonField to extract data from the return string, insert the data to target table, a simple job design looks like:
tRest--main--tExtractXMLField--main--tMysqlOutput.

Please read the documentation of these two components and learn them.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Seven Stars

Re: How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

Hi,

I have URL for API.

below is the extracted data from the component tRest 

job is : tRest----main---tlogrow

 

<?xml version="1.0" encoding="ISO-8859-15"?>

-<root>

-<row>

<Body><GetTaxResult> <ResultCode>Success</ResultCode> <DocDate>2015-12-08</DocDate> <Timestamp>2017-05-04T07:45:39.119779Z</Timestamp> <TotalAmount>10</TotalAmount> <TotalDiscount>0</TotalDiscount> <TotalExemption>0</TotalExemption> <TotalTaxable>10</TotalTaxable> <TotalTax>0.72</TotalTax> <TotalTaxCalculated>0.72</TotalTaxCalculated> <TaxDate>2015-12-08</TaxDate> <DocCode>364cfaa2-6567-48a2-afed-040cf35c905e</DocCode> <TaxLines><TaxLine><LineNo>01</LineNo> <TaxCode>P0000000</TaxCode> <Taxability>true</Taxability> <BoundaryLevel>Zip5</BoundaryLevel> <Exemption>0</Exemption> <Discount>0</Discount> <Taxable>10</Taxable> <Rate>0.071250</Rate> <Tax>0.72</Tax> <TaxCalculated>0.72</TaxCalculated> <TaxDetails><TaxDetail><Country>US</Country> <Region>MN</Region> <JurisType>State</JurisType> <JurisCode>27</JurisCode> <Taxable>10</Taxable> <Rate>0.068750</Rate> <Tax>0.69</Tax> <JurisName>MINNESOTA</JurisName> <TaxName>MN STATE TAX</TaxName> </TaxDetail> <TaxDetail><Country>US</Country> <Region>MN</Region> <JurisType>Special</JurisType> <JurisCode>80002</JurisCode> <Taxable>10</Taxable> <Rate>0.002500</Rate> <Tax>0.03</Tax> <JurisName>TRANSIT IMPROVEMENT TAX</JurisName> <TaxName>MN SPECIAL TAX</TaxName> </TaxDetail> </TaxDetails> </TaxLine> </TaxLines> <TaxAddresses><TaxAddress><AddressCode>01</AddressCode> <City>Eagan</City> <Country>US</Country> <PostalCode>55123</PostalCode> <Region>MN</Region> <TaxRegionId>2076383</TaxRegionId> <JurisCode>2700000000</JurisCode> </TaxAddress> </TaxAddresses> </GetTaxResult> </Body>

<ERROR_CODE/>

</row>

</root>

 

It's tough to identify the XPath query.

 

 

Community Manager

Re: How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

The Rest Web service returns a XML string, you can use tExtractXMLField to extract the data you want, what data do you want to extract? Take a look at tExtractXMLField component and let me know if you have troubles to use this component.

----------------------------------------------------------
Talend | Data Agility for Modern Business
Seven Stars

Re: How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

Hello ArvinRapt,

My Scenario 2 is Instead of Giving values in HTTP body either XML/Json,I would like to pass dynamic values in my url.

please have a look at the screenshot.

httpbody.PNGInstead of passing these values in Body, Is there any possibilities to give DocDate values dynamically in the URL.

So that we can retrieve data based on date.

if it is possible then have to load those data into the table, only the fields which are all necessary.

Seven Stars

Re: How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

Well it depends on how the API that you are calling works. If the API has the capability to fetch the values based on the date, then absolutely.

You just have to use a context variable for the docDate field and reference it in the URL as per the format that the API requires.

 

Do you have a sample URL request that can fetch values based on the docDate field? and do you need to execute this request for a number of dates? Depending on these, your job design might vary.