Skip to Content

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]

     GO

    SET ANSI_NULLS ON

     GO

     SET QUOTED_IDENTIFIER ON

     GO


    CREATE PROCEDURE [dbo].[InsertCustomers]

          @XMLCustomers as XML,

             @ProcessedFlag as nvarchar(1),

             @InsertedBy as nvarchar(10)

     AS

     BEGIN

         SET NOCOUNT ON;

         INSERT Customers (CustomerID, CustomerName, ProcessedFlag, InsertedBy)

            SELECT

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

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

               @ProcessedFlag as ProcessedFlag,

               @InsertedBy as InsertedBy

            FROM

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

     END


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”>

          <Customers>

               <Customer>

                    <ID>500</ID>

                    <Name>Item 500</Name>

               </Customer>

               <Customer>

                    <ID>501</ID>

                    <Name>Item 501</Name>

               </Customer>

          </Customers>

     </MT_JDBC_Customers>


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


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

          <Statement>

               <dbTableName action=”EXECUTE”>

                    <table>InsertCustomers</table>

                    <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>

               </dbTableName>

          </Statement>

     </ns0:MT_JDBC_StoredProc_Request>

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:

StoredProc_InsertXML.png

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″?>

     <ROWSET>

          <ROW num=”1″>

               <EMPID>10</EMPID>

               <EMPNAME>Perry Smith</EMPNAME>

               <EMPJOB>Manager</EMPJOB>

               <EMPSAL>800</EMPSAL>

          </ROW>

          <ROW num=”1″>

               <EMPID>20</EMPID>

               <EMPNAME>John Calvach</EMPNAME>

               <EMPJOB>Principal Support Consultant</EMPJOB>

               <EMPSAL>900</EMPSAL>

          </ROW>

     </ROWSET>

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

     insCtx DBMS_XMLSave.ctxType;

     rows number;

     begin

          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

     end;


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