Recently I’ve seen the question about using Table type parameter in DB stored procedure call using SAP JDBC receiver adapter.

According to SAP’s help, following SQL data types are supported: INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (input and output), CLOB (input and output), CURSOR (output; only in connection with the Oracle JDBC driver).

It means that we can’t use any other type for parameters passed to stored procedure.

But let’s take a look at very interesting types existing in famous database systems, such as MS SQL and Oracle (not sure about others) – “XML” type for MS SQL and “XMLType” type for Oracle. Generaly speaking these types hold XML structures and make it available for SQL.

I would want to show how we can use XML data types to fulfill above requirement.

Let’s take a test scenario where we want to insert multiple customer records in MS SQL Server database with one call using stored procedure. Our stored procedure will look like:

     USE [Test_DB]






    CREATE PROCEDURE [dbo].[InsertCustomers]

          @XMLCustomers as XML,

             @ProcessedFlag as nvarchar(1),

             @InsertedBy as nvarchar(10)



         SET NOCOUNT ON;

         INSERT Customers (CustomerID, CustomerName, ProcessedFlag, InsertedBy)


               CUST.item.value(‘(ID)[1]’, ‘nvarchar(10)’) as ‘CustomerID’,

               CUST.item.value(‘(Name)[1]’, ‘nvarchar(10)’) as ‘CustomerName’,

               @ProcessedFlag as ProcessedFlag,

               @InsertedBy as InsertedBy


               @XMLCustomers.nodes(‘/Customers/Customer’) as CUST(item)


Here you can see that “XML” type parameter XMLCustomers is used for retrieving customers records from XML string with SQL statement.

I won’t describe all my test JDBC scenario as it’s quite simple and there are plenty of JDBC scenario guides on SDN.

Our request message looks like:

     <MT_JDBC_Customers action=”insert” insertedby=”PID”>




                    <Name>Item 500</Name>




                    <Name>Item 501</Name>




After request mapping we get JDBC message format for stored procedure call:

     <ns0:MT_JDBC_StoredProc_Request xmlns:ns0=”urn://train”>


               <dbTableName action=”EXECUTE”>


                    <XMLCustomers type=”CLOB” isInput=”true”>![CDATA[<Customers> <Customer> <ID>500</ID> <Name>Item 500</Name> </Customer> <Customer> <ID>501</ID> <Name>Item 501</Name> </Customer> </Customers>]]</XMLCustomers>

                    <ProcessedFlag type=”VARCHAR” isInput=”true”>I</ProcessedFlag>

                    <InsertedBy type=”VARCHAR” isInput=”true”>PID</InsertedBy>




As you can see, I’ve put Customers recordset into XMLCustomers parameter value with type “CLOB” and wrapped it with CDATA tag (special thanks to “Return as XML” option in graphical mapping 🙂 ).

After performing stored procedure call we can see result in target DB:


That’s all. We’ve just inserted recordset in target database table with one shot. I think it was not so difficult 😉 .

For Oracle database I found the following example (but didn’t try it myself):

     <?xml version=”1.0″?>


          <ROW num=”1″>


               <EMPNAME>Perry Smith</EMPNAME>




          <ROW num=”1″>


               <EMPNAME>John Calvach</EMPNAME>

               <EMPJOB>Principal Support Consultant</EMPJOB>




     create or replace procedure InsertXML(xmlDoc IN VARCHAR2, tableName IN VARCHAR2) is

     insCtx DBMS_XMLSave.ctxType;

     rows number;


          insCtx := DBMS_XMLSave.newContext(tableName); — get the context handle

          rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); — this inserts the doent

          dbms_output.put_line(to_char(rows) || ‘ rows inserted’);

          DBMS_XMLSave.closeContext(insCtx); — this closes the handle


Regards, Evgeniy.

P.S. I would like to thank Amit Srivastava for pointing me at this approach 🙂

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply