How to pass XML to DB using XMLTYPE
Posted
by James Taylor
on Oracle Blogs
See other posts from Oracle Blogs
or by James Taylor
Published on Tue, 23 Mar 2010 23:43:19 +1300
Indexed on
2010/03/24
1:03 UTC
Read the original article
Hit count: 758
Probably not a common use case but I have seen it pop up from time to time. The question how do I pass XML from a queue or web service and insert it into a DB table using XMLTYPE.
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 11.1.1.2 using composite and mediator to link the web service with the DB adapter.
1. Insert Database Objects
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
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.
singleString.xsd
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.
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
SQL Statement
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 11.1.1.2 using composite and mediator to link the web service with the DB adapter.
1. Insert Database Objects
--Create XML_EXAMPLE_TBL
CREATE
TABLE XML_EXAMPLE_TBL (PAYLOAD XMLTYPE);
CREATE or
REPLACE PROCEDURE load_test_xml (xmlFile in CLOB) IS
BEGIN
INSERT INTO xml_example_tbl (payload) VALUES
(XMLTYPE(xmlFile));
--Handle
the exceptions
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20101, 'Exception
occurred in loadPurchaseOrder procedure :'||SQLERRM || ' **** ' || xmlFile );
END
load_test_xml;
/
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
When selected Empty Composite click Finish.
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.
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.
Select Call a Stored Procedure or Function then click Next
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.
singleString.xsd
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.
Import the schema file by selecting the import schema icon
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.
You have now completed the SOA Component you can now save your work, deploy and test.
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
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
SQL Statement
select * from xml_example_tbl;
© Oracle Blogs or respective owner
