3 Replies Latest reply on Mar 11, 2009 11:41 AM by ama1

Smooks DB->XML

ama1 Mar 9, 2009 12:35 PM

Hi Forum,
I try to write data from a DB to XML. Unfortunatley the documentation about enrichment is not very detailed.
I have a xml doc with that structure:

<dossiers>
 <dossier id="1" />
 <dossier id="2" />
</dossiers>

and a table with products:

dossierId, productNo, productName,...

goal is this:

<dossiers>
 <dossier id="1">
 <product no="123" name="onetwothree"/>
 </dossier>
 <dossier id="2">
 <product no="456" name="xy"/>
 <product no="789" name="xx"/>
 </dossier>
</dossiers>

I want to hold (if possible) only one product row in memory, due to the product table is very big.
I guess I need to use the resultSetRowSelector for this, but I found really no documentation about that.
Can anyone provide me some documentation or even an example. I would really appreciate!!!

1. Re: Smooks DB->XML

tfennelly Mar 10, 2009 1:08 PM (in response to ama1)

Have you seen the quickstart: huge-split-enrich-transform-route?

So firstly what you need to do is define the datasource, which would look something like:

<smooks-resource-list xmlns="http://www.milyn.org/xsd/smooks-1.1.xsd"
 xmlns:ds="http://www.milyn.org/xsd/smooks/datasource-1.1.xsd">

 <!--
 Configure the "OrderManagement" DB access datasource...
 -->
 <ds:direct bindOnElement="$document" datasource="productDatasource" autoCommit="false"
 driver="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost:9002"
 username="sa" password="" />

</smooks-resource-list>

You'll need to select the id values from each dossier message fragment:

<smooks-resource-list xmlns="http://www.milyn.org/xsd/smooks-1.1.xsd"
 xmlns:jb="http://www.milyn.org/xsd/smooks/javabean-1.1.xsd">

 <jb:bindings beanId="dossier" class="java.util.HashMap" createOnElement="dossier">
 <jb:value property="id" data="dossier/@id" decoder="Integer" />
 </jb:bindings>

</smooks-resource-list>

(Obviously you can merge the configs into a single smooks config).

Next, you'll need to select the product record from the DB. You can select all products into a single resultset (you can also scope that around the "APPLICATION" so as not to be reading it every time) and then use row selector to select the product. For this, lets just read the specific product record for every dossier:

<smooks-resource-list xmlns="http://www.milyn.org/xsd/smooks-1.1.xsd"
 xmlns:db="http://www.milyn.org/xsd/smooks/db-routing-1.1.xsd">

 <db:executor executeOnElement="dossier" datasource="productDatasource">
 <db:statement>select productNo, productName from PRODUCTS where dossierId = ${dossier.id}</db:statement>
 <db:resultSet name="product" />
 </db:executor>

</smooks-resource-list>

(Note how we use the dossier jb:binding data in the query)

And then we need to apply a template to each dossier fragment to add the product info:

<smooks-resource-list xmlns="http://www.milyn.org/xsd/smooks-1.1.xsd"
 xmlns:ftl="http://www.milyn.org/xsd/smooks/freemarker-1.1.xsd">

 <ftl:freemarker applyOnElement="dossier">
 <ftl:template><!--<product no="${product[0].productNo}" name="${product[0].productName}"/>--></ftl:template>
 <ftl:use>
 <ftl:inline directive="addto" />
 </ftl:use>
 </ftl:freemarker>

</smooks-resource-list>

Note how we have to reference the zero element in the product result set. This is because we don't use the row selector.

Again, all these configs can go into a single Smooks config.

2. Re: Smooks DB->XML

tfennelly Mar 10, 2009 1:10 PM (in response to ama1)

I forgot to mention.... I didn't try this out... it's totally manufactured so you might have to play with it a bit :)
Actions
3. Re: Smooks DB->XML

ama1 Mar 11, 2009 11:41 AM (in response to ama1)

Hi Tom,
great! the inline directive was the missing link. The transformation works now fine :-)

Thanks for the help!!!
Actions

Go to original post