Skip to Content
Author's profile photo Evgeniy Kolmakov

Using recordset as input parameter for MS SQL Server stored procedure call.

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 🙂

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.