tHttpRequest with XML response content to tExtractXMLField or tXMLMap

One Star

tHttpRequest with XML response content to tExtractXMLField or tXMLMap

Talend 5.0.2 but also tried this with 4.2.2
I am trying to retrieve an xml document from a whitepages.com using their free api with basic get parameters and then write the xml data to a database.
I had very little trouble with the xml doc retrieval using tHttpRequest.
Here is the real problem. The ResponseContent shows in a tLogRow. It is written to the file. The file can be read as XML File input perfectly. But the ResponseContent doesn't seem to work, as a flow through (row#->) to either tExtractXMLField or tXMLMap.
The tExtractXMLField just doesn't produce any output.
For the tXMLMap I had to try converting the ResponseContent with tConvertType before connecting it to the tXMLMap because the ResponseContent is a String and tXMLMap requires the XML input to be a "document" type. That failed on the tConvertType.
Am I missing something? I've been at this for a few hours and it seems like I must be.
As a side note: I think there may be a bug in tHttpRequest in that if the "Write response content to file" isn't specified the ResponseContent isn't sent out with the flow to the "Main" row. Since it works when the "write to file" is specified I need to move passed this now. Will address as a possible bug elsewhere.
Hope someone can point me in the write direction. The XML doc is coming from whitepages.com so it is available for testing by anyone. They offer api access (returning an xml doc) from their database using an http request. Limited number of requests per day with free api key (200 or 300) and no more than one every 2 seconds. Just need to request api key which comes back in auto response.
They also offer JSON response so I am going to try something with that if this doesn't work.
One Star

Re: tHttpRequest with XML response content to tExtractXMLField or tXMLMap

Got it figured out. I had the Loop XPath query wrong. I used the one from the XML input instead of the XSD file input schema.
One Star

Re: tHttpRequest with XML response content to tExtractXMLField or tXMLMap

Hi
I am trying to do a similar thing but new to talend so need help.
I have a webservice call using tsoap. was able to submit a request (soap xml) and get the response back (soap response).
My task is to parse the records from that response xml and store it in db with some manipulations.
Xml response data is a complex structure. like account account lines account details....
Could someone suggest what is the best way to achieve this (components and job design)
-thanks
One Star

Re: tHttpRequest with XML response content to tExtractXMLField or tXMLMap

if you've retrieved the XML data then you are most of the way there. In a basic job you will likely pass the Retrieved XML file to the tExtractXMLfield component then to a tMap component and then Output it to whatever you want. The tExtractXMLField component is a little tricky to set up since you have to know the XML schema setup and the Loop XPath query.
To understand this better you could take a sample of your XML file, or a XML Schema file (.xsd) if you have one, and set it up as an XML datasource. Then drag it into the job as an Input and look at the component info to see how it defines the fields. This is just a suggestion for a short cut.
Hope that helps.
One Star

Re: tHttpRequest with XML response content to tExtractXMLField or tXMLMap

Sample response:
<DWPOWSExportReply xmlns="urn:AribaSmiley Very HappyEV9r1:vcsv" partition="pcsv" variant="vcsv">
<ERPOrder_PurchOrderWSExport_Item><item><OrderMethodCategory>ERP</OrderMethodCategory><StatusString>Ordered</StatusString>
<UniqueName>EP343600</UniqueName><OrderMethod>config.java.oracle.server.BuysensePOERPCC</OrderMethod></item>
</ERPOrder_PurchOrderWSExport_Item><ERPOrder_PurchOrderWSExportLineDetails_Item><item><LineItems><item><Description>
<UnitOfMeasure><UniqueName>EA</UniqueName></UnitOfMeasure><Price><Amount>7.99000</Amount></Price>
<Description>9r1 description 1</Description><ItemNumber /></Description><ShipTo><UniqueName>A13602</UniqueName>
</ShipTo></item><item><Description><UnitOfMeasure><UniqueName>EA</UniqueName></UnitOfMeasure><Price>
<Amount>7.99000</Amount></Price><Description>9r1 description 1</Description><ItemNumber /></Description>
<ShipTo><UniqueName>A13602</UniqueName></ShipTo></item><item><Description><UnitOfMeasure><UniqueName>EA</UniqueName>
</UnitOfMeasure><Price><Amount>7.99000</Amount></Price><Description>9r1 description 1</Description><ItemNumber />
</Description><ShipTo><UniqueName>A13602</UniqueName></ShipTo></item></LineItems><UniqueName>EP343600</UniqueName></item>
</ERPOrder_PurchOrderWSExportLineDetails_Item><ERPOrder_PurchOrdWSExportAcctDetails_Item><item><LineItems><item>
<Accountings><SplitAccountings><item><FieldDefault4><UniqueName>EVA001:A136VITA:Field4:NYC</UniqueName></FieldDefault4><Amount><Amount>7.99000</Amount></Amount><FieldDefault3><UniqueName>EVA001:A136VITA:Field3:T8</UniqueName></FieldDefault3><FieldDefault2><UniqueName>EVA001:A136VITA:Field2:1????</UniqueName></FieldDefault2><FieldDefault5><UniqueName>EVA001:A136VITA:Field5:A1234</UniqueName></FieldDefault5><Percentage>100.00000</Percentage><NumberInCollection>1</NumberInCollection><Quantity>0.00000</Quantity><FieldDefault1><UniqueName>EVA001:A136VITA:Field1:AA12</UniqueName></FieldDefault1></item><item><FieldDefault4><UniqueName>EVA001:A136VITA:Field4:NYC</UniqueName></FieldDefault4><Amount><Amount>7.99000</Amount></Amount><FieldDefault3><UniqueName>EVA001:A136VITA:Field3:T8</UniqueName></FieldDefault3><FieldDefault2><UniqueName>EVA001:A136VITA:Field2:1????</UniqueName></FieldDefault2><FieldDefault5><UniqueName>EVA001:A136VITA:Field5:A1234</UniqueName></FieldDefault5><Percentage>100.00000</Percentage><NumberInCollection>2</NumberInCollection><Quantity>0.00000</Quantity><FieldDefault1><UniqueName>EVA001:A136VITA:Field1:AA12</UniqueName></FieldDefault1></item></SplitAccountings></Accountings><ReceivingType>2</ReceivingType></item><item><Accountings><SplitAccountings><item><FieldDefault4><UniqueName>EVA001:A136VITA:Field4:NYC</UniqueName></FieldDefault4><Amount><Amount>0.00000</Amount></Amount><FieldDefault3><UniqueName>EVA001:A136VITA:Field3:T8</UniqueName></FieldDefault3><FieldDefault2><UniqueName>EVA001:A136VITA:Field2:1????</UniqueName></FieldDefault2><FieldDefault5><UniqueName>EVA001:A136VITA:Field5:A1234</UniqueName></FieldDefault5><Percentage>0.00000</Percentage><NumberInCollection>1</NumberInCollection><Quantity>1.00000</Quantity><FieldDefault1><UniqueName>EVA001:A136VITA:Field1:AA12</UniqueName></FieldDefault1></item><item><FieldDefault4><UniqueName>EVA001:A136VITA:Field4:NYC</UniqueName></FieldDefault4><Amount><Amount>0.00000</Amount></Amount><FieldDefault3><UniqueName>EVA001:A136VITA:Field3:T8</UniqueName></FieldDefault3><FieldDefault2><UniqueName>EVA001:A136VITA:Field2:1????</UniqueName></FieldDefault2><FieldDefault5><UniqueName>EVA001:A136VITA:Field5:A1234</UniqueName></FieldDefault5><Percentage>0.00000</Percentage><NumberInCollection>2</NumberInCollection><Quantity>1.00000</Quantity><FieldDefault1><UniqueName>EVA001:A136VITA:Field1:AA12</UniqueName></FieldDefault1></item></SplitAccountings></Accountings><ReceivingType>2</ReceivingType></item><item><Accountings><SplitAccountings><item><FieldDefault4><UniqueName>EVA001:A136VITA:Field4:NYC</UniqueName></FieldDefault4><Amount><Amount>0.00000</Amount></Amount><FieldDefault3><UniqueName>EVA001:A136VITA:Field3:T8</UniqueName></FieldDefault3><FieldDefault2><UniqueName>EVA001:A136VITA:Field2:1????</UniqueName></FieldDefault2><FieldDefault5><UniqueName>EVA001:A136VITA:Field5:A1234</UniqueName></FieldDefault5><Percentage>50.00000</Percentage><NumberInCollection>1</NumberInCollection><Quantity>0.00000</Quantity><FieldDefault1><UniqueName>EVA001:A136VITA:Field1:AA12</UniqueName></FieldDefault1></item><item><FieldDefault4><UniqueName>EVA001:A136VITA:Field4:NYC</UniqueName></FieldDefault4><Amount><Amount>0.00000</Amount></Amount><FieldDefault3><UniqueName>EVA001:A136VITA:Field3:T8</UniqueName></FieldDefault3><FieldDefault2><UniqueName>EVA001:A136VITA:Field2:1????</UniqueName></FieldDefault2><FieldDefault5><UniqueName>EVA001:A136VITA:Field5:A1234</UniqueName></FieldDefault5><Percentage>50.00000</Percentage><NumberInCollection>2</NumberInCollection><Quantity>0.00000</Quantity><FieldDefault1><UniqueName>EVA001:A136VITA:Field1:AA12</UniqueName></FieldDefault1></item></SplitAccountings></Accountings><ReceivingType>2</ReceivingType></item></LineItems><UniqueName>EP343600</UniqueName></item></ERPOrder_PurchOrdWSExportAcctDetails_Item></DWPOWSExportReply>
Can you help me design a job to get each of this element.
I tried with xmlextract but not clear how to put the xpath to retrieve all the elements.
Thanks
One Star

Re: tHttpRequest with XML response content to tExtractXMLField or tXMLMap

As stated above....use the XML File wizard to help you figure out what the XMLExtract should look like. Look in the Help file as well. That's how I did it. The XMLExtract settings and XMLInput settings are very similar.

To use the XML File wizrd right click on XML FILE under the connections and choose the "Create new XML File" (that's from memory so it may not be exactly that but close). There are good resources in the tutorials and in the help for the wizard and how you use it. Once you have created the XMLFile connection you drag it into the job area and choose tXMLInput. Now look under the settings and you will see the similarity to the tXMLExtractField settings.
I'm not a real XML expert so this is what I suggest as a short cut. It helped me understand the whole thing better. That and a couple hours of reading. But isn't that always the way.