How to pass XML to DB using XMLTYPE
By James Taylor on Mar 23, 2010
In this example I create a basic table with the field PAYLOAD of type XMLTYPE. I then take the full XML payload of the web service and insert it into that database for auditing purposes.
I use SOA Suite 22.214.171.124 using composite and mediator to link the web service with the DB adapter.
1. Insert Database Objects
CREATE TABLE XML_EXAMPLE_TBL (PAYLOAD XMLTYPE);
CREATE or REPLACE PROCEDURE load_test_xml (xmlFile in CLOB) IS
INSERT INTO xml_example_tbl (payload) VALUES (XMLTYPE(xmlFile));
--Handle the exceptions
WHEN OTHERS THEN
raise_application_error(-20101, 'Exception occurred in loadPurchaseOrder procedure :'||SQLERRM || ' **** ' || xmlFile );
2. Creating New SOA Project TestXMLTYPE in JDeveloper
In JDeveloper either create a new Application or open an existing Application you want to put this work.
Under File -> New -> SOA Tier -> SOA Project
Provide a name for the Project, e.g. TestXMLType
Choose Empty Composite
3. Create Database Connection to Stored Procedure
A Blank composite will be displayed. From the Component Palette drag a Database Adapter to the External References panel. and configure the Database Adapter Wizard to connect to the DB procedure created above.
Provide a service name InsertXML
Select a Database connection where you installed the table and procedure above. If it doesn't exist create a new one.
Choose the schema you installed your Procedure in step 1 and query for the LOAD_TEST_XML procedure.
Click Next for the remaining screens until you get to the end, then click Finish to complete the database adapter wizard.
4. Create the Web Service Interface
Download this sample schema that will be used as the input for the web service. It does not matter what schema you use this solution will work with any. Feel free to use your own if required.
Drag from the component palette the Web Service to the Exposed Services panel on the component.
Provide a name InvokeXMLLoad for the service, and click the cog icon.
Click the magnify glass for the URL to browse to the location where you downloaded the xml schema above.
Browse to the location to where you downloaded the singleString.xsd above.
Click OK for the Import Schema File, then select the singleString node of the imported schema.
Accept all the defaults until you get back to the Web Service wizard screen. The click OK. This step has created a WSDL based on the schema we downloaded earlier.
Your composite should now look something like this now.
5. Create the Mediator Routing Rules
Drag a Mediator component into the middle of the Composite called Components
Give the name of Route, and accept the defaults
Link the services up to the Mediator by connecting the reference points so your Composite looks like this.
6. Perform Translations between Web Service and the Database Adapter.
From the Composite double click the Route Mediator to show the Map Plan. Select the transformation icon to create the XSLT translation file.
Choose Create New Mapper File and accept the defaults.
From the Component Palette drag the get-content-as-string component into the middle of the translation file.
Your translation file should look something like this
Now we need to map the root element of the source 'singleString' to the XMLTYPE of the database adapter, applying the function get-content-as-string.
To do this drag the element singleString to the left side of the function get-content-as-string and drag the right side of the get-content-as-string to the XMLFILE element of the database adapter so the mapping looks like this.
When you deploy I have assumed that you have the correct database configurations in the WebLogic Console based on the connection you setup connecting to the Stored Procedure.
7. Testing the Application
Open Enterprise Manager and navigate to the TestXMLTYPE Composite and click the Test button.
Load some dummy variables in the Input Arguments and click the 'Test Web Service' button
Once completed you can run a SQL statement to check the install. In this instance I have just used JDeveloper and opened a SQL Worksheet
select * from xml_example_tbl;
Result, you should see the full payload in the result.