How to pass XML to DB using XMLTYPE

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


--Create XML_EXAMPLE_TBL


CREATE TABLE XML_EXAMPLE_TBL (PAYLOAD XMLTYPE);


--Create procedure LOAD_TEST_XML

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



CreateSOAProject.JPG
  
Provide a name for the Project, e.g. TestXMLType
 
createXMLTYPEproject.JPG


Choose Empty Composite

XMLTYPEComposite.JPG


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

ServiceInsertXML.JPG

 
Select a Database connection where you installed the table and procedure above. If it doesn't exist create a new one.

DBConnection.JPG
 
Select Call a Stored Procedure or Function then click Next

SelectProcedure.JPG


Choose the schema you installed your Procedure in step 1 and query for the LOAD_TEST_XML procedure.

QueryLoadTestXMLProcedure.JPG


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.

WSCreateInvokeXML.JPG



Click the magnify glass for the URL to browse to the location where you downloaded the xml schema above.

SearchSchema.JPG
 

Import the schema file by selecting the import schema icon

ImportSchema.JPG


Browse to the location to where you downloaded the singleString.xsd above.

BrowseSchema.JPG


Click OK for the Import Schema File, then select the singleString node of the imported schema.

ChooseNode.JPG


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.

WSCompleteInvokeXML.JPG


Your composite should now look something like this now.

XMLTypeIncompleteComposite.JPG


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

CreateRouteMediator.JPG


Link the services up to the Mediator by connecting the reference points so your Composite looks like this.

XMLTypeCompleteComposite.JPG


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.

MediatorXMLLoadIncomplete.JPG


Choose Create New Mapper File and accept the defaults.

XMLLoadCreateTransformation.JPG


From the Component Palette drag the get-content-as-string component into the middle of the translation file.

getContentasString.JPG


Your translation file should look something like this

XMLLoadTransformationIncomplete.JPG


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.

XMLLoadTransformationComplete.JPG
 
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.

XMLLoadEM.JPG


Load some dummy variables in the Input Arguments and click the 'Test Web Service' button


XMLLoadPayload.JPG

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;


Result, you should see the full payload in the result.

XMLLoadResult.JPG



Comments:

cool example!
I am trying to pass XML payload from a B2B Adapter to DB Adapter.

The DB Adapter calls a PL-SQL Procedure with an input Parameter - of XMLType.
Im having trouble mapping using Mediator.
1) When I map directly from

B2B Adapter ns1:root_element(with associated xsd) to db:input_param (XML Type)
It says Auto Map found no matches.
Can you advise?

Posted by guest on September 19, 2012 at 02:03 PM GMT+13:00 #

Another way to do this is using varrays.

here is a sample, hope it makes sense.

create table red_rock_tbl (
RED_ROCK_ID NUMBER);

drop type red_rock_type;
drop type insert_rec_type;
drop type insert_line_type;

CREATE OR REPLACE TYPE insert_line_type AS OBJECT
( INSERT_ID NUMBER
);
/

CREATE OR REPLACE TYPE insert_rec_type AS VARRAY(9999) OF insert_line_type;
/

CREATE OR REPLACE TYPE red_rock_type AS OBJECT
( PAYLOAD INSERT_REC_TYPE
);
/

create or replace package red_rock_pkg

is

procedure insert_row( p_insert_payload in red_rock_type
, x_rows_inserted out varchar2);

end red_rock_pkg;
/

create or replace package body red_rock_pkg

is

procedure insert_row( p_insert_payload in red_rock_type
, x_rows_inserted out varchar2)

is

l_payload red_rock_type;
l_payload_line insert_line_type;
l_line_count number := 0;

begin

l_payload := p_insert_payload;

for i in 1..l_payload.payload.count loop

l_payload_line := l_payload.payload(i);
l_line_count := l_line_count + 1;

insert into red_rock_tbl values ( l_payload_line.insert_id);

end loop;

x_rows_inserted := l_line_count;

end insert_row;

end red_rock_pkg;
/

Posted by James Taylor on September 19, 2012 at 05:20 PM GMT+13:00 #

Thanks James!
Somehow I was able to pass the XML payload from B2B_Service using Mediator to DB_Adapter

In Mediator, I used the xsl get-content-as-string(/ns1:B2BRequest) function.
I changed the called PLSQL input parameters to tak a CLOB input.
I directly Wired from the get-content-as-string to the DB_Adapter Eclob Input Param.
create or replace procedure process_B2BXML (input_clob_xml clob)
...
The PLSQL function saw the XML payload fine, only had to cast to XMLType inside PLSQL:
epodreqXMLType :=SYS.XMLType.createXML(input_clob_xml);
Thanks, -D

Posted by guest on September 22, 2012 at 06:36 AM GMT+13:00 #

I am trying to retrive a CLOB Object form Oracle DB using JCA Adapter.When creating adapter it genedrates a XSD with CLOB_TYPE only field.Now i want to consume this messages using OSB ,But creating proxies based on the JCA Adaopter doesn't able to consume any message from AQ.

Need urgent help on this?please ,if you have done such poc please help m e

Posted by guest on April 08, 2013 at 06:42 AM GMT+13:00 #

I'm a little confused by your use case. Can you confirm what technology you want to use for the source and the target, i.e. DB Poll to AQ.

I would expect the CLOB to be exposed. The issue is if you need to transform the payload. If you just want to pass from the DB to the AQ then you won't need a transformation, just use the $body variable for the target of the AQ.

If you can provide more input I may be able to provide more suggestions.

cheers
James

Posted by guest on April 08, 2013 at 10:18 AM GMT+13:00 #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Discussions and Examples using Oracle Fusion Middleware. Some image links are broken when using Firefox, Safari, and Chrome. If you want to see the full image please use IE.

Twitter:@james8001

tumblr hit counter vistors, thanks for your support

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
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
   
       
Today