Howto Quickly Insert XML into a Database using Mule ESB

Posted on 12 June 2010 by Pascal Prins

Mule ESB is a versatile service bus that contains numerous usable components out-of-the-box. In this blog I’ll show you how to process an incoming XML document, break it up into multiple parts and insert them into the database without using any custom Java classes.

In this example we will be using this (simple) XML document as source input. The document contains a collection of Customers.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

<?xml version="1.0" encoding="UTF-8"?>
<customers>
   <customer>
      <firstName>Steve</firstName>
      <lastName>Jobs</lastName>
      <address>
         <street>1 Infinite Loop</street>
         <city>Cupertino</city>
         <country>USA</country>
      </address>
   </customer>
   <customer>
      <firstName>Bill</firstName>
      <lastName>Gates</lastName>
      <address>
         <street>1 Microsoft Way</street>
         <city>Washington</city>
         <country>USA</country>
      </address>
   </customer>
</customers>

After breaking the XML up into multiple Customer entities, we will insert them into the following database structure:

1
2
3
4
5
6
7

create table CUSTOMERS (
   first_name VARCHAR2(32)
,  last_name VARCHAR2(64)
,  street VARCHAR2(128)
,  city VARCHAR2(128)
,  country VARCHAR2(3)
);

To split the XML document into multiple Customer entities, we’ll use an outbound splitting router like this one:

1
2
3

<expression-splitter-router evaluator="xpath" expression="//Customer">
   <stdio:outbound-enpdoint system="OUT"/>
</expression-splitter-router>

The xpath evaluator mentioned in the expression-splitter-router excepts both Dom and String objects. This means we can just use plain XML files (no need to create a w3c.dom.Document first. It’ll be parsed on-the-fly.

Once the message has been split, we want each Customer to be inserted into a database. The trick here is to use the JDBC transport. We’ll need to configure a connector in which
“named queries” can be configured. A “named query” is a PODS (~Plain Old Dml Statement) identified by a key.

1
2
3
4

    <jdbc:connector name="jdbcConnector" dataSource-ref="jdbcDataSource">
        <jdbc:query key="insertIntoFoobar"
                    value="insert into FOOBAR (id, code, description) VALUES (#[map-payload:ID], #[map-payload:CODE], #[map-payload:DESCRIPTION])"/>
    </jdbc:connector>

In this example, a Map payload is expected from which the values are derived. When using XML, an XPATH expression evaluator seems more appropriate (see final solution).

PS: It might be nice to know that unnamed queries are also supported.

I’ve introduced the basics, now let’s have a look at the complete Mule configuration:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesource.org/schema/mule/core/2.2"
      xmlns:spring="http://www.springframework.org/schema/beans"
      xmlns:vm="http://www.mulesource.org/schema/mule/vm/2.2"
      xmlns:xm="http://www.mulesource.org/schema/mule/xml/2.2"
      xmlns:jdbc="http://www.mulesource.org/schema/mule/jdbc/2.2"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:context="http://www.springframework.org/schema/context"
      xsi:schemaLocation="
      http://www.mulesource.org/schema/mule/core/2.2 http://www.mulesource.org/schema/mule/core/2.2/mule.xsd
      http://www.mulesource.org/schema/mule/vm/2.2 http://www.mulesource.org/schema/mule/vm/2.2/mule-vm.xsd
      http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
      http://www.mulesource.org/schema/mule/xml/2.2 http://www.mulesource.org/schema/mule/xml/2.2/mule-xml.xsd
      http://www.mulesource.org/schema/mule/jdbc/2.2 http://www.mulesource.org/schema/mule/jdbc/2.2/mule-jdbc.xsd
      http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd">
 
    <context:property-placeholder location="classpath:my.properties"/>
 
    <spring:bean id="jdbcDataSource" class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
        <spring:property name="driverName" value="${db.driverName}"/>
        <spring:property name="url" value="${db.url}"/>
    </spring:bean>
 
    <jdbc:connector name="jdbcConnector" dataSource-ref="jdbcDataSource">
        <jdbc:query key="insertCustomer"
                    value="insert into CUSTOMERS (first_name, last_name, street, city, country) VALUES (#[xpath://Customer/FirstName],#[xpath://Customer/LastName],#[xpath://Customer/Address/Street],#[xpath://Customer/Address/City],#[xpath://Customer/Address/Country])"/>
    </jdbc:connector>
 
    <xm:namespace-manager includeConfigNamespaces="true">
        <xm:namespace prefix="cus" uri="http://www.redstream.nl/schema/customer/1.0"/>
    </xm:namespace-manager>
 
    <model>
 
        <service name="processCustomers">
            <inbound>
                <vm:inbound-endpoint path="processCustomersService"/>
            </inbound>
 
            <outbound>
                <expression-splitter-router evaluator="xpath" expression="//Customer">
                    <jdbc:outbound-endpoint queryKey="insertCustomer"/>
                </expression-splitter-router>
            </outbound>
        </service>
 
    </model>
 
</mule>

Et voila … using the above configuration, you will be able to process incoming XML and insert the contents in a database.

Some final notes:

  • Use a property-placeholder to configure the database credentials.
  • When incoming XML contains namespaces, they could globally be configured using a namespace-manager
  • Notice that the jdbc:outbound-endpoint refers to the named query by key
  • The named query will be “injected” with a single Customer entity, accessible by the XPATH expression evaluator
tags: ,

About Pascal Prins

Started in 1996. Eye for detail and environment. Broad experience, mainly covering integration related technologies, tools and solutions. Involved in many projects as technical lead, architect or business consultant. One wife, two beautiful daughters, one magnificent son ... and one cat.

One Response to Howto Quickly Insert XML into a Database using Mule ESB

  1. Pingback: Tweets die vermelden Howto Quickly Insert XML into a Database using Mule ESB | Redstream Blog -- Topsy.com

Meest gelezen