SAP Cloud Integration (CPI/HCI) || JDBC Adapter (Part 3) || Update the DB using JDBC adapter via a Stored Procedure (Parameter as XML Type)

[Sookriti_Mishra]

Hello CPI Enthusiasts,

This series covers all about JDBC Adapter, and here are list of blogs:
Part 1: Pre-requisites and Configuration
Part 2: Update the DB using JDBC adapter via a Stored Procedure (Fields as parameter)
Part 3: Update the DB using JDBC adapter via a Stored Procedure (Parameter as XML Type)

And, this is Part 3:

In Part 1, we saw how to configure a JDBC Adapter, and in Part 2, we saw how to update field value using JDBC Adapter but using one entry at a time.

Well, when I implemented Part 2, it was a disaster 💥 and here is a before and after:


It's sad right?

So, here is the solution. Okay, disclaimer, I am no DB expert but a friend who is an expert helped me write a Stored Procedure in which he provisioned a Parameter which was of type XML, Cloud Integration (CPI) could pass the entire payload in just one go, into the Parameter of XML type.

The stored procedure would look something like this:

USE [SID] GO 

/****** Object:  StoredProcedure [dbo].[storedProcedureName]    Script Date: 29/9/2021 8:16:52 AM ******/

SET 

  ANSI_NULLS ON GO 

SET 

  QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[storedProcedureName] @XMLParameterName as XML AS BEGIN 

SET 

  NOCOUNT ON;

begin tran begin try INSERT INTO [dbo].storedProcedureName ([FIELD1], [FIELD2], [FIELD3]) 

SELECT 

  CUST.item.value('(FIELD1)[1]', 'nvarchar(250)') as 'FIELD1', 

  CUST.item.value('(FIELD2)[1]', 'nvarchar(250)') as 'FIELD2', 

  CUST.item.value('(FIELD3)[1]', 'nvarchar(250)') as 'FIELD3', 

  GETDATE() AS [PROCESSDATE] 

FROM 

  @XMLParameterName.nodes('/ElementName/item') as CUST(item) commit tran end try begin catch DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT 

  @ErrorMessage = ERROR_MESSAGE(), 

  @ErrorSeverity = ERROR_SEVERITY(), 

  @ErrorState = ERROR_STATE();

--Print @ErrorMessage

RAISERROR (

  @ErrorMessage, @ErrorSeverity, @ErrorState

);

rollback tran end catch END

 

---

---

I connected with our DB Expert Baraneetharan (Click here to follow him on LinkedIn), to understand the stored procedure, and in a one-one-interview with him, here is what he has to say.

Sookriti: Hey Baranee. First of all, thank you so much for writing the stored procedure. I have a few questions, are you ready?

Baranee:

Sookriti: Haha. This meme is a delight for a "The Office" fan. So, please tell our readers a little about the stored procedure.

Baranee: The functionality behind this stored proc is to read data from payload and load to destination SQL DB. As mentioned in previous post, you can call the stored proc by passing table field inputs as parameters, then stored proc will do insert operation for you. Below is the sample stored proc format for your reference,

CREATE PROCEDURE PROCEDURE_NAME @parameter1 varchar(250), 

@ parameter2 varchar(250), 

@ parameter3 varchar(250) AS BEGIN INSERT INTO TABLE_NAME ([column1], [column2], [column3) 

VALUES 

  (

    @parameter1, @ parameter2, @ parameter3

  ) END

Above stored proc works better when you have few records to be inserted into the destination, since stored proc is called for every record set to insert. When your payload has huge record set and n number of columns to be loaded in destination table, there comes the performance impact. But no worries, we have an alternate solution to overcome the performance impact which fits better for huge column and record set.

Instead calling stored proc multiple times by passing column values as inputs parameters, we can call the stored proc only once with entire record set as single parameter. Here we pass entire payload in an XML format to stored proc as an XML String parameter. Stored proc will read the entire XML data as string, get the record set split as individual records based on the node values from string and get those records inserted into the destination in secs. Below is the sample stored proc format for your reference,

CREATE PROCEDURE PROCEDURE_NAME @XMLparameter as XML AS BEGIN 

SET 

  NOCOUNT ON;

INSERT INTO TABLE_NAME ([column1], [ column2], [ column3) 

SELECT 

  CUST.item.value('(column1)[1]', 'nvarchar(250)') as column1, 

  CUST.item.value('(column2)[1]', 'nvarchar(250)') as column2, 

  CUST.item.value('(column3)[1]', 'nvarchar(250)') as column3 

FROM 

  @ XMLparameter.nodes(

    '/node1/node2) as CUST(item)



END

---

After having this stored procedure written, here is what the integration flow will look like:

The target structure (XSD) to call the Stored Procedure would be (2nd Message Mapping block as mentioned in the above screenshot):

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:element name="ElementName">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="StatementName" maxOccurs="unbounded" minOccurs="0">

          <xs:complexType>

            <xs:sequence>

              <xs:element name="storedProcedureName">

                <xs:complexType>

                  <xs:sequence>

                    <xs:element type="xs:string" name="table"/>

                    <xs:element name="XMLParameterName" maxOccurs="unbounded" minOccurs="0">

                      <xs:complexType>

                        <xs:simpleContent>

                          <xs:extension base="xs:string">

                            <xs:attribute type="xs:string" name="type"/>

                            <xs:attribute type="xs:string" name="isInput"/>

                          </xs:extension>

                        </xs:simpleContent>

                      </xs:complexType>

                    </xs:element>

                  </xs:sequence>

                  <xs:attribute type="xs:string" name="action"/>

                </xs:complexType>

              </xs:element>

            </xs:sequence>

          </xs:complexType>

        </xs:element>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

</xs:schema>

Now, here is what we need to pass into this structure?

Here are the constants:

• /ElementName/StatementName/storedProcedureName/@action: EXECUTE


• /ElementName/StatementName/storedProcedureName/table: storedProcedureName


• /ElementName/StatementName/storedProcedureName/XMLParameterName/@type: CLOB


• /ElementName/StatementName/storedProcedureName/XMLParameterName/@isInput: true

The output payload would look something like below:

<?xml version="1.0" encoding="UTF-8"?>

<ElementName>

    <StatementName>

        <storedProcedureName action="EXECUTE">

            <table>storedProcedureName</table>

            <XMLParameterName type="CLOB">

                <![CDATA[



<ElementName>

    <item>

        <Field1>Value1.1</Field1>

        <Field2>Value2.1</Field2>

        <Field3>Value3.1</Field3>

    </item>

    <item>

        <Field1>Value1.2</Field1>

        <Field2>Value2.2</Field2>

        <Field3>Value3.2</Field3>

    </item>

    <item>

        <Field1>Value1.3</Field1>

        <Field2>Value2.3</Field2>

        <Field3>Value3.3</Field3>

    </item>

</ElementName>



]]>

            </XMLParameterName>

        </storedProcedureName>

    </StatementName>

</ElementName>

You have already seen the Before and After images, but I am posting it again for special effects. Haha 😂

 

Okay, then. 🥳🙋‍♀️
Hope you had a good read.

If you wish to know more, or have any queries on this blog, then please feel free to drop a comment.


Follow me on linked in by clicking here.

Thanks & Regards,

Sookriti Mishra

[former_member687189]

much awaited blog. Thank you so much for this. 🙂

[arkesh_sharma]

Its a nice blog!

I have a similar requirement but without doing the mapping and with multiple upsert statements. I am wondering and trying to figure out how will my XML SQL Query look like in that case. It'll be helpful if you/DB expert can provide with a XML SQL query Stored Procedure statement for multiple upserts.

[yeeloon-khoo]

Multiple upsert is slow, I do have an example of XML SQL bulk stored procedure(see below SP and sample payload), can send one big xml, bulk upsert without looping inside stored prodecure, using SQL relational/set based update/insert. Tested bulk upsert 1000 records from CPI to Database average took about 0.5 second (0.5s is just simple run, not complete detail performance check).

Below example from my published SAP CPI course at this link To see step-by-step how it work, other JDBC example or other CPI topics, if interested can enroll the course. Thanks.

-------------------------------------------------------
Create Stored Procedure XMLBatchUpsert_SpecialCustomers
-------------------------------------------------------
CREATE PROCEDURE XMLBatchUpsert_SpecialCustomers
@XMLData XML
AS
BEGIN
SELECT
CUST.item.value('(CustomerID)[1]', 'varchar(5)') as CustomerID,
CUST.item.value('(CompanyName)[1]', 'varchar(40)') as CompanyName
INTO #TempSpecialCustomers
FROM
@XMLData.nodes('/root/item') as CUST(item)

UPDATE SpecialCustomers
SET CompanyName = T.CompanyName
FROM SpecialCustomers AS C, #TempSpecialCustomers AS T
WHERE C.CustomerID = T.CustomerID

INSERT INTO SpecialCustomers (CustomerID, CompanyName)
SELECT CustomerID, CompanyName
FROM #TempSpecialCustomers AS T
WHERE NOT EXISTS(SELECT * FROM SpecialCustomers WHERE CustomerID = T.CustomerID)
END

------------------------------------------
EXECUTE UPSERT - Multiple Record - XML SQL
------------------------------------------
<root>
<statement>
<storedProcedureName action="EXECUTE">
<table>XMLBatchUpsert_SpecialCustomers</table>
<XMLData isInput="true" type="CLOB"><![CDATA[<root>
<item>
<CustomerID>AAAAA</CustomerID>
<CompanyName>AA Name</CompanyName>
</item>
<item>
<CustomerID>BBBBB</CustomerID>
<CompanyName>BB Name</CompanyName>
</item>
<item>
<CustomerID>CCCCC</CustomerID>
<CompanyName>CC Name</CompanyName>
</item>
<item>
<CustomerID>DDDDD</CustomerID>
<CompanyName>DD Name</CompanyName>
</item>
</root>]]></XMLData>
</storedProcedureName>
</statement>
</root>

[arjun_thakur]

Hi Sookriti,

I followed the steps that you have mentioned, but i am not getting the data to be passed to the stored procedure in the following tag in the payload when i check that in trace:

<![CDATA[

]]>

Can you please advise.

Regards,

Arjun