[resolved] Mapping SOAP response to Mysql Output

Seven Stars

[resolved] Mapping SOAP response to Mysql Output

Hello,
I am creating a job that consumes a webservice through a tSOAP component.
I am taking the output of this component in Document format.
And now I want to dump the SOAP body content into a Mysql database table using tMysqlOutput component.
So I have created a tXMLMap component .
The soap response returned by the webservice is as follows:
<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<GetXmlDataResponse xmlns="http://smartbear.com">
<GetXmlDataResult>
<x:books xmlns:x="urn:books">
<book xmlns="" id="bk001">
<author>Writer</author>
<title>The First Book</title>
<genre>Fiction</genre>
<price>44.95</price>
<pub_date>2000-10-01</pub_date>
<review>An amazing story of nothing.</review>
</book>
<book xmlns="" id="bk002">
<author>Poet</author>
<title>The Poet's First Poem</title>
<genre>Poem</genre>
<price>24.95</price>
<review>Least poetic poems.</review>
</book>
</x:books>
</GetXmlDataResult>
</GetXmlDataResponse>
</soap:Body>
</soap:Envelope>

However when I run the job, it gets the whole response as a single row.
But it does not dump any data into the database table because the element values are retrieved as null.
See TOSJobOutput.png attached.
Can anyone tell me the issues with mapping done?
Or point me to any other post or tutorial that explains how to map a SoapResponse document to individual database columns.

Accepted Solutions
Community Manager

Re: [resolved] Mapping SOAP response to Mysql Output

Hi
The return result of web service is a string with XML format, you can use tExtractXMLField component to extract the value such as The First Book from the title node after tSOAP,
tSOAP--main--tExtactXMLField--main--tMysqlOutput
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Moderator

Re: [resolved] Mapping SOAP response to Mysql Output

Hi,
Could you use tLogrow instead of tMysqlOutput to see the actual result? Is there still error "Column bookid cannot be null" in console?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seven Stars

Re: [resolved] Mapping SOAP response to Mysql Output

Yes, I have done that. The output (Soap response in italics) is also shown in the first post.
One Star

Re: [resolved] Mapping SOAP response to Mysql Output

You need to map the xml to your schema in the db output.
Seven Stars

Re: [resolved] Mapping SOAP response to Mysql Output

I have mapped the SOAP response to tMysqlOutput component through a tXmlMap component. See the attached image in my first post.
The problem is that it is not looping through the two book tags to get the data but treats the whole response as a single row. I am not able to figure out how to make the mapping so that each book tag is treated as a single row so that it can be inserted into the mysql db.
One Star

Re: [resolved] Mapping SOAP response to Mysql Output

Sorry, missed the tXmlMap. You need to loop on book not envelope.
Seven Stars

Re: [resolved] Mapping SOAP response to Mysql Output

I tried that too. I looped on book yet it shows only 1 row going into the tXmlMap and 0 rows coming out of it.
Community Manager

Re: [resolved] Mapping SOAP response to Mysql Output

Hi
The return result of web service is a string with XML format, you can use tExtractXMLField component to extract the value such as The First Book from the title node after tSOAP,
tSOAP--main--tExtactXMLField--main--tMysqlOutput
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Seven Stars

Re: [resolved] Mapping SOAP response to Mysql Output

Thanks shong, tExtractXmlField works like a charm!!