VDB to process WebService data source which responds with XML inside a String field as CDATA

[sonyjop]

Hello

I am trying to get hands on JBoss VDB for first time.

I have a use case where need to consider a webservice as a data source. This webservice returns the data inside a String field as a CDATA.

eg:

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

      <MySoapResponse xmlns="http://tempuri.org/">

         <MySoapResult><![CDATA[<?xml version="1.0"?>

  <TAG1>

  <TAG2>

  <TAG3>

  <TAG4  units="Hz">31.5Hz</TAG4>

  <TAG5  units="BPD">3084.31BPD</TAG5>

  <TAG3>

  </TAG2>

  </TAG1>]]></MySoapResult>

      </MySoapResponse>

   </soap:Body>

</soap:Envelope>

I am not sure how to capture this to process the items as Column names with say, TAG4, TAG5 ..

Any help would be highly appreciated.

thanks

[shawkins]

Typically you would process this with XMLTABLE · Teiid Documentation

Your context xpath will depend on whether the envelope/body tags are included.  Assuming they are, you would use something like:

... from xmltable('/*:Envelope/*:Body/MySoapResponse/MySoapResult' PASSING doc COLUMNS TAG4 string, TAG5 string) x

Where doc is blob/clob/xml value for the document passed from the webservice invocation.

Teiid Designer Teiid Tooling - JBoss Community should also be able to assist in generating the sql for these scenarios.