Skip to Content
Technical Articles
Author's profile photo Yee Loon Khoo

SAP CPI – JDBC Adapter – Upsert Single & Bulk using Direct SQL, Splitter and Stored Procedure with XML Payload without Looping

Hello CPI Integrators,

This blog post focus on SAP CPI JDBC UPSERT operation, below videos show example on how to perform single upsert and bulk upsert, Direct SQL and stored procedure approach, and Splitter looping and Stored procedure XML Bulk Upsert without Looping (even inside stored procedure).

SAP CPI – JDBC Adapter – UPSERT Single & Bulk using Direct SQL & Stored Procedure with XML Payload:

SAP CPI – JDBC Adapter – Performance Upsert Each Row using Splitter vs Upsert using XML Bulk Upsert:

Below are SQL/Stored procedure used in above videos:

CREATE TABLE – Direct SQL (you can use this to create same table to try out examples):

CREATE TABLE [dbo].[SpecialCustomers](
	[CustomerID] [nchar](5) NOT NULL,
	[CompanyName] [nvarchar](40) NOT NULL
 CONSTRAINT [PK_SpecialCustomers] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

 

UPSERT – Single Record – Direct SQL:

UPDATE SpecialCustomers SET CompanyName = 'A Name from Update'
WHERE CustomerID = 'AAAAA';
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO SpecialCustomers (CustomerID, CompanyName)
    VALUES ('AAAAA', 'A Name from Insert');
END

 

Create Stored Procedure Upsert_SpecialCustomer:

CREATE PROCEDURE Upsert_SpecialCustomer
	@CustomerID varchar(5),
	@CompanyName varchar(40)
AS
BEGIN
	UPDATE SpecialCustomers SET CompanyName = @CompanyName
	WHERE CustomerID = @CustomerID;
	IF @@ROWCOUNT = 0
	BEGIN
		INSERT INTO SpecialCustomers (CustomerID, CompanyName)
		VALUES (@CustomerID, @CompanyName);
	END
END

 

EXECUTE UPSERT – Single Record – Direct SQL :

Upsert_SpecialCustomer @CustomerID = 'AAAAA', @CompanyName = 'A Name Upsert';

 

EXECUTE UPSERT – Single Record – XML SQL :

<root>
    <statement>
        <storedProcedureName action="EXECUTE">
            <table>Upsert_SpecialCustomer</table>
            <CustomerID isInput="true" type="VARCHAR">AAAAA</CustomerID>
            <CompanyName isInput="true" type="VARCHAR">A Name from SP</CompanyName>
        </storedProcedureName>
    </statement>
</root>

 

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>

 

Hope you found above content useful.

 

Above videos from my published SAP CPI course. If interested on other CPI JDBC lessons or CPI topics, can check out the course at this link. Thanks.

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Marcelo Berger
      Marcelo Berger

      Hi Yee,

       

      Thank you for your blog, this is exactly what we're looking to implement, but with CPI sending data to HANA Cloud.

       

      Do you happen to have an example of the  Create Stored Procedure XMLBatchUpsert_SpecialCustomers but specifically created in HANA, as it seems the syntax is slightly different in HANA from the one you provide.

       

      Much appreciated,

       

      Marcelo

      Author's profile photo Yee Loon Khoo
      Yee Loon Khoo
      Blog Post Author

      Hi Marcelo,

      Not done in SAP Hana db/stored prodecure before, yes, syntax and way to handle xml is different between mssql and hana db.

      Tried check a bit, below might be relevant to apply same concept:

       

      Create local temp table in hana db:

      http://www.hanaexam.com/p/sap-hana-temporary-tables.html?m=1

       

      Read xml string to xml table in hana db:

      https://blogs.sap.com/2017/08/14/processing-xml-data-in-sap-hana/

       

      Possible can dynamic create temp table by select from xml table?

      https://www.kodyaz.com/sap-abap/create-table-on-hana-database-using-select-statement-dynamically.aspx

       

      then the rest is standard SQL stuff.

       

      Above is what i can find out although not tried before, hope it help u somehow.

       

      Regards,

      Yee Loon

      Author's profile photo Marcelo Berger
      Marcelo Berger

      Thanks Yee,

       

      I had seen the xml to string to xml table blog earlier today, but it didn't have an example of how to encapsulate the logic within the stored procedure, which is what I need.

       

      That is the only missing piece for now. I'll continue to dig around, and if you happen to play around with it in a HANA DB and figure it out, please share your findings 🙂

       

      Thank you!

      Author's profile photo Arjun Singh Thakur
      Arjun Singh Thakur

      Hi Yee Loon,

      How do we get the data to be passed to the stored procedure in inside CDATA in the mapping step. Can you please advise.

      Regards,

      Arjun