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

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

Anonymous — Thu, 04 May 2017 07:19:45 GMT

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.

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

Anonymous — Thu, 04 May 2017 08:33:38 GMT

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.

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

Anonymous — Thu, 04 May 2017 10:04:33 GMT

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.

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.

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

Anonymous — Thu, 04 May 2017 10:06:43 GMT

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

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

Anonymous — Thu, 04 May 2017 10:34:04 GMT

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.

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

Anonymous — Thu, 04 May 2017 10:37:57 GMT

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.

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

Anonymous — Thu, 04 May 2017 12:09:35 GMT

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.

Instead 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.

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

Anonymous — Thu, 04 May 2017 12:30:41 GMT

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.