Read XML Data from File(Inbound) and write it to SQLServer2012 (Outbound) using Spring Bean in Mule Studio

Introduction

Earlier article we have seen as how to insert record into PostgreSQL after reading from a specified source (xml).PostgreSQL offers four default database sources to connect with.They are Derby, MySQL, Oracle and PostgreSQL Data Source.

However,if we want to establish a JDBC Connectivity to other database like SQL Server 2012 (as in our case), we can do so by creating a Spring Bean that will act as a Datasource. In the bean we need to specify

1. Driver Class name
2. Server Name
3. Port number in which the server is listening to
4. Database Name
5. User Name
6. Password

e.g.the value of the above properties for the below image will be

What we need to have for doing the experiment?

We need

1. Sql Server 2012
2. Microsoft JDBC Driver 4.0 for SQL Server
3. Download Mule ESB

4. A database to be created in SqlServer 2012 say "TestDB" where atleast one Table say "tblFamily" should be created.

   			-- Table Creation Script
   			CREATE TABLE [dbo].[tblFamily]
   			(
   				[Title] [varchar](50) NOT NULL,
   				[FirstName] [varchar](50) NOT NULL,
   				[LastName] [varchar](50) NOT NULL
   			)
   		

5. An XML file from where the data needs to be read.The XML file structure will be as under

   

6. Everything should be up and running properly

Let us start the experiment

Step1

First of all, let us create a mule project.

Step2:Import the Postgre SQL JDBC Driver

In the Package Explorer, right-click over the Project folder ->BuildPath->Add External Archives….Choose the location where the "sqljdbc4.jar" jar file is located in the hard drive and then click on the "Open" button

Now we can find the jar file being present in the "Referenced Libraries" of the package explorer.

This will allow us to create an instance of the Object driver

Step3:Now the time for creating DataSource

Switch to the "Global Elements" tab.Click on the "Create" button.Look for Beans - > Bean and click the OK button.

The Bean Window appears

Enter "SqlServer_JDBC_DataSource" in the "ID" textbox and "org.enhydra.jdbc.standard.StandardDataSource" in the "Class" textbox.

Click on the "Add" button in the "Bean Subelements" section.And from the available "Subelements" choose the "Add Property".

From the property window, enter "driverName" in the "Name" textbox and "com.microsoft.sqlserver.jdbc.SQLServerDriver" in the "Value" textbox

Click "Finish".Again choose the "Add Property" and enter "url" in the "Name" textbox and "jdbc:sqlserver://Denali_Server:1433;databaseName=TestDB;user=sa;password=tiger" in the "Value" textbox

So the complete Bean property window should look like

Click "OK" button

Step4:Let us create the Connector now

So we have successfully created a DataSource with it's proper driver.Now we need a Connector.

(a)Click on the "Create" button.Look for Connector > Database.Then click OK.

(b)From the Database Window that appears, click on the "Bean" dropdown and choose "SqlServer_JDBC_DataSource".It is the Data-Source that we created in the previous step

(c)Switch to the "Queries" tab and click on the button ("Add Query"). First we need to specify the "Query Key"(which is "InsertRecord" here) and then the query as shown under.Click "OK" button.

INSERT INTO tblFamily(Title, FirstName, LastName) VALUES (#[xpath('//Title').text],#[xpath('//FirstName').text],#[xpath('//LastName').text])

Finally, we will be presented with the below screen if everything has gone right so far

If at this stage we view the "Configuration XML" tab, it will look like

<spring:beans>
	<spring:bean id="SqlServer_JDBC_DataSource" name="Bean" class="org.enhydra.jdbc.standard.StandardDataSource">
		<spring:property name="url" value="jdbc:sqlserver://Denali_Server:1433;databaseName=TestDB;user=sa;password=tiger"/>
		<spring:property name="driverName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
	</spring:bean>
</spring:beans>
<jdbc:connector name="Database" dataSource-ref="SqlServer_JDBC_DataSource" validateConnections="true" queryTimeout="-1" pollingFrequency="0" doc:name="Database">
	<jdbc:query key="InsertRecord" value="INSERT INTO tblFamily(Title, FirstName, LastName) VALUES (#[xpath('//Title').text],#[xpath('//FirstName').text],#[xpath('//LastName').text])"/>
</jdbc:connector>

Step5:Let us create flow now

Switch to the "Configuration XML" tab first and add the below piece of code just after the "<jdbc:connector>"

<flow name="testxmlFlow1" doc:name="testxmlFlow1">
    
	<!-- Step 1: XML file source -->    	
	<file:inbound-endpoint path="D:\InputLocation" responseTimeout="10000" doc:name="Source"/>
	
	<!-- Step 2: Convert between byte arrays and strings -->        
	<byte-array-to-string-transformer doc:name="Byte-Array-to-String"/>
	
	<!-- Step 3: Split each row using XPath -->        
	<splitter evaluator="xpath" expression="/Families/Family" doc:name="Splitter"/>
	
	<!-- Step 4: Dump into the destination Database -->        
	<jdbc:outbound-endpoint exchange-pattern="one-way" queryKey="InsertRecord" queryTimeout="-1" connector-ref="Database" doc:name="Destination"/>
        
</flow>

It is quite simple.At the first step, we basically use the "File" component(InBound) to locate the XML file.

In the second step, we basically convert between byte arrays and strings by using the "byte-array-to-string-transformer"

In the third step, we basically parse the XML file value by using the "Splitter" component.We specify a valid Mule XPath expression for the purpose.

In the last step , we push the records to the PostgreSQL Database which is the "Database" component (Outbound).

The final flow is

The full configuration XML should look like

<spring:beans>
        <spring:bean id="SqlServer_JDBC_DataSource" name="Bean" class="org.enhydra.jdbc.standard.StandardDataSource">
            <spring:property name="url" value="jdbc:sqlserver://Denali_Server:1433;databaseName=TestDB;user=sa;password=tiger"/>
            <spring:property name="driverName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        </spring:bean>
    </spring:beans>
    <jdbc:connector name="Database" dataSource-ref="SqlServer_JDBC_DataSource" validateConnections="true" queryTimeout="-1" pollingFrequency="0" doc:name="Database">
        <jdbc:query key="InsertRecord" value="INSERT INTO tblFamily(Title, FirstName, LastName) VALUES (#[xpath('//Title').text],#[xpath('//FirstName').text],#[xpath('//LastName').text])"/>
    </jdbc:connector>
    
    <flow name="testxmlFlow1" doc:name="testxmlFlow1">
    
	<!-- Step 1: XML file source -->    	
	<file:inbound-endpoint path="D:\InputLocation" responseTimeout="10000" doc:name="Source"/>
	
	<!-- Step 2: Convert between byte arrays and strings -->        
	<byte-array-to-string-transformer doc:name="Byte-Array-to-String"/>
	
	<!-- Step 3: Split each row using XPath -->        
	<splitter evaluator="xpath" expression="/Families/Family" doc:name="Splitter"/>
	
	<!-- Step 4: Dump into the destination Database -->        
	<jdbc:outbound-endpoint exchange-pattern="one-way" queryKey="InsertRecord" queryTimeout="-1" connector-ref="Database" doc:name="Destination"/>
        
</flow>

Now run the application.We will find that the record has been inserted.

Conclusion

So in this article we have understood as how to insert record into SQL Server 2012 database from Mule studio by using Spring Beans.Hope this will be helpful.Thanks for reading