You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@camel.apache.org by shin938 <sh...@gmail.com> on 2012/03/01 18:17:55 UTC
how to build a sql insert route from xml payload
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.
Re: how to build a sql insert route from xml payload
Posted by shin938 <sh...@gmail.com>.
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.
Re: how to build a sql insert route from xml payload
Posted by Babak Vahdat <ba...@swissonline.ch>.
The Fusesource video is f awsome!
Unbelievable what James & Co. has made out of the Fuse-IDE Tool!
Congratulations :-)
I'm really impressed!
Babak
Am 02.03.12 09:20 schrieb "Claus Ibsen" unter <cl...@gmail.com>:
>On Thu, Mar 1, 2012 at 7:23 PM, shin938 <sh...@gmail.com> 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-x
>>>ml-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-x
>>>ml-payload-tp5528543p5528645.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=unsubs
>>>cribe_by_code&node=5528543&code=c2hhbG9tOTM4QGdtYWlsLmNvbXw1NTI4NTQzfDEx
>>>MDc1ODU2NDM=>.
>>> 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%3A
>>>email.naml>
>>>
>>
>>
>> --
>> View this message in context:
>>http://camel.465427.n5.nabble.com/how-to-build-a-sql-insert-route-from-xm
>>l-payload-tp5528543p5528724.html
>> Sent from the Camel - Users mailing list archive at Nabble.com.
>
>
>
>--
>Claus Ibsen
>-----------------
>FuseSource
>Email: cibsen@fusesource.com
>Web: http://fusesource.com
>Twitter: davsclaus, fusenews
>Blog: http://davsclaus.blogspot.com/
>Author of Camel in Action: http://www.manning.com/ibsen/
Re: how to build a sql insert route from xml payload
Posted by Claus Ibsen <cl...@gmail.com>.
On Thu, Mar 1, 2012 at 7:23 PM, shin938 <sh...@gmail.com> 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
>> <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-tp5528543p5528724.html
> Sent from the Camel - Users mailing list archive at Nabble.com.
--
Claus Ibsen
-----------------
FuseSource
Email: cibsen@fusesource.com
Web: http://fusesource.com
Twitter: davsclaus, fusenews
Blog: http://davsclaus.blogspot.com/
Author of Camel in Action: http://www.manning.com/ibsen/
Re: how to build a sql insert route from xml payload
Posted by shin938 <sh...@gmail.com>.
Hi Claus
Thank you for the quick response.
aside from mybatis or velocity , I understand it wouldn't be possible
with the xpath language?
if I had a java bean in my queue I could do it with the in.body language
right?
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
> <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-tp5528543p5528724.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: how to build a sql insert route from xml payload
Posted by Claus Ibsen <cl...@gmail.com>.
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 <sh...@gmail.com> 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: cibsen@fusesource.com
Web: http://fusesource.com
Twitter: davsclaus, fusenews
Blog: http://davsclaus.blogspot.com/
Author of Camel in Action: http://www.manning.com/ibsen/