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.
Solved! Go to Solution.
I just share my idea here someone please help me out.
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).
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.
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.
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"?>
<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>
It's tough to identify the XPath query.
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.
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.