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