Returning raw XML string from Oracle XDB within Groovy / Grails
Here’s a quick solution for trying to extract the raw xml string from an Oracle XDB database when working with Grails.
After an initial attempt to hardcode a hibernate query to return the raw sql using .getStringVal() as follows:
SELECT x.id, x.xmlData xmldata.getStringVal(), lines.*
FROM xml_requests x
XMLTable('declare default element namespace
"http://www.foo.com/fooservice";
for $i in /XXXResponse/ResponseData/*/node()
where $i/*:Error/*:Code = "0"
return $i'
PASSING x.xmldata
COLUMNS GivenName NVARCHAR2(200)
PATH './*:PersonAdresseData/*:Person/*:Navn/*:Fornavne',
Surname NVARCHAR2(200)
PATH './*:PersonAdresseData/*:Person/*:Navn/*:Efternavn') lines
WHERE ....
I found that the query would blow up if the xml string returned exceeded some threshold. Approx ~ 2000 chars as I’m using an oracle database.
Solution was relatively simple thanks to the various support libraries Oracle brings to the table.
Add the xdb.jar and xmlparserv2.jar libraries from the Oracle installation into your Grails application /lib directory and then modify the .gsp page to handle the custom type accordingly as below:
<div class="list">
<table>
<thead>
<tr>
<g:each var="key" in="${list[0].keySet()}">
<g:sortableColumn property="${key}" title="${key}"/>
</g:each>
</tr>
</thead>
<tbody>
<g:each in="${list}" status="i" var="item">
<tr class="${(i % 2) == 0 ? 'odd' : 'even'}">
<g:each var="key" in="${list[0].keySet()}">
<td>
<g:if test="${item[key] instanceof oracle.xdb.XMLType}">
${item[key].getStringVal()}
</g:if>
<g:else>
${item[key]}
</g:else>
</td>
</g:each>
</tr>
</g:each>
</tbody>
</table>
</div>
** The above code will dynamically render a table view using the column names as headers in an HTML table. In my eaxmple one the columns I wanted to display was XMLType and stored in Oracle XDB format.
