Thanks.
I did try it with setting headers first but then I had a problem:
because I transformed the pax logging event to a pojo and then to xml
with xstream, if for example there was no exception then there was no
field in the xml and I wouldn't know how to design the query. at the end
I did it with a java bean that creates a list of parameters,including
null elements for non existing fields, and updated the db with sql
component:
<camelContext id="routerContext"
xmlns="http://camel.apache.org/schema/spring";>
<route id="logEventsToDBRouter">
<from uri="activemq:topic:com.log.logsxmltopic"/>
<unmarshal ref="xstream"/>
<to uri="bean:logEventDBProcessor"/>
<to uri="sql:insert into
log_events(uuid,level,message,timestamp,time_zone_id,category,
thread_name,bundle_name,bundle_version,exception)
values(#,#,#,#,#,#,#,#,#,#)?dataSourceRef=dataSource"/>
</route>
</camelContext>
Thank you for your help.
On 03/02/12 10:21, Claus Ibsen-2 [via Camel] wrote:
> On Thu, Mar 1, 2012 at 7:23 PM, shin938 <[hidden email]
> </user/SendEmail.jtp?type=node&node=5530328&i=0>> wrote:
> > Hi Claus
> > Thank you for the quick response.
> > aside from mybatis or velocity , I understand it wouldn't be possible
> > with the xpath language?
>
> You may be able to do some pieces with a scripting language ala
> groovy, but it will just get a bit messy.
>
> However if you want all in XML, then I have seen people store values
> as headers from the xpath, and then refer to these headers
> in a template language like velocity, or even the simple language of
> Camel.
>
> 1. set headers first with xpath expressions
> 2. build the SQL using velocity/simple etc.
>
> There is a video example of this approach for building a WS request in
> a similar way
> http://fusesource.com/products/fuse-ide/fuse-ide-browsing-and-tracing/
>
>
> > if I had a java bean in my queue I could do it with the in.body
> language
> > right?
> >
>
> A java bean you can use bean parameter bindings to get the xpath stuff
> as parameters to your bean.
>
> See
> http://camel.apache.org/bean-binding.html
> http://camel.apache.org/parameter-binding-annotations.html
>
> If you got the Camel in Action book, then chapter 4, section 4.5.4 has
> xpath examples.
>
> With xpath mind about namespaces, if you XML uses namespaces, your
> xpath expression must do that as well.
>
> > Thanks.
> >
> > On 03/01/12 19:52, Claus Ibsen-2 [via Camel] wrote:
> >> Hi
> >>
> >> Just use a java bean and use java code to build the SQL.
> >> Or a template language such as velocity / freemarker.
> >>
> >> But often a java bean can do the trick in 5 lines of code.
> >>
> >> For more complicated SQL, then MyBatis have a java based SQL builder
> >> which looks cool.
> >> They also offer a XML templates to build dynamic queries. Its worth a
> >> look if you have a fair amount of SQL work to do.
> >> http://www.mybatis.org/
> >>
> >> And there is a Camel component for it as well
> >> http://camel.apache.org/mybatis
> >>
> >>
> >> On Thu, Mar 1, 2012 at 6:17 PM, shin938 <[hidden email]
> >> </user/SendEmail.jtp?type=node&node=5528645&i=0>> wrote:
> >>
> >> > Hi
> >> > I created a route in servicemix that sends pax loging events to a
> >> queue and
> >> > transforms them to xml into another queue.
> >> > now i want a route that builds a sql insert from the xml payloads,
> >> but I
> >> > can't figure out the way to do that.
> >> >
> >> > I think I want something like that if it was possible:
> >> > <route>
> >> > <from uri="activemq:logsxmlqueue"/>
> >> > <setBody>
> >> > <simple>insert into log_table
> >> > values(xpath(//level),xpath(//category),xpath(//message)) </simple>
> >> > </setBody>
> >> > <to uri="jdbc:mysqldb"/>
> >> > </route>
> >> >
> >> > Thank you for any help.
> >> >
> >> > --
> >> > View this message in context:
> >>
> http://camel.465427.n5.nabble.com/how-to-build-a-sql-insert-route-from-xml-payload-tp5528543p5528543.html
> >> > Sent from the Camel - Users mailing list archive at Nabble.com.
> >>
> >>
> >>
> >> --
> >> Claus Ibsen
> >> -----------------
> >> FuseSource
> >> Email: [hidden email] </user/SendEmail.jtp?type=node&node=5528645&i=1>
> >> Web: http://fusesource.com
> >> Twitter: davsclaus, fusenews
> >> Blog: http://davsclaus.blogspot.com/
> >> Author of Camel in Action: http://www.manning.com/ibsen/
> >>
> >>
> >>
> ------------------------------------------------------------------------
> >> If you reply to this email, your message will be added to the
> >> discussion below:
> >>
> http://camel.465427.n5.nabble.com/how-to-build-a-sql-insert-route-from-xml-payload-tp5528543p5528645.html
> >>
> >> To unsubscribe from how to build a sql insert route from xml payload,
> >> click here
> >> <
> >> NAML
> >>
> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml
> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>>
>
> >>
> >
> >
> > --
> > View this message in context:
> http://camel.465427.n5.nabble.com/how-to-build-a-sql-insert-route-from-xml-payload-tp5528543p5528724.html
> > Sent from the Camel - Users mailing list archive at Nabble.com.
>
>
>
> --
> Claus Ibsen
> -----------------
> FuseSource
> Email: [hidden email] </user/SendEmail.jtp?type=node&node=5530328&i=1>
> Web: http://fusesource.com
> Twitter: davsclaus, fusenews
> Blog: http://davsclaus.blogspot.com/
> Author of Camel in Action: http://www.manning.com/ibsen/
>
>
> ------------------------------------------------------------------------
> If you reply to this email, your message will be added to the
> discussion below:
> http://camel.465427.n5.nabble.com/how-to-build-a-sql-insert-route-from-xml-payload-tp5528543p5530328.html
>
> To unsubscribe from how to build a sql insert route from xml payload,
> click here
> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5528543&code=c2hhbG9tOTM4QGdtYWlsLmNvbXw1NTI4NTQzfDExMDc1ODU2NDM=>.
> NAML
> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
--
View this message in context:
http://camel.465427.n5.nabble.com/how-to-build-a-sql-insert-route-from-xml-payload-tp5528543p5530689.html
Sent from the Camel - Users mailing list archive at Nabble.com.
