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

      10 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 Rahma Ben Abdallah
      Rahma Ben Abdallah

      Hello Marcelo ,

       

      We're also looking to implement with CPI sending data to HANA Cloud,

      Have you found a solution to upsert data from CPI to HANA DB ?

       

      Thank you in advance,

      Rahma.

       

      Author's profile photo Joel Langoyan
      Joel Langoyan

      Hi Rahma Ben Abdallah,

      Were you able to figure this out? Need to do the same for one of my scenario.

      Thanks,

      Joel

      Author's profile photo Philippe Addor
      Philippe Addor

      Hi Yee Loon

      That's very helpful information! Exactly what I needed. Excellent work.

       

      Thank you so much,

      Philippe

       

       

      Author's profile photo Chaitanya Kumar Mangalarapu
      Chaitanya Kumar Mangalarapu

      Hi Yee Loon,

       

      What if the structure of the source xml changes? Is there a way to create the table structure dynamically depending on the xml structure?

       

      Regards,

      Chaitanya.

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

      Hi Chaitanya,

      You looking for dynamically create table and columns based on input xml? Don't think this is possible, since example here those table and columns are fixed and already pre-defined, but just insert or update rows only.

      Author's profile photo Rutuja Thakre
      Rutuja Thakre

      Hi Yee Loon Khoo,

      Your blog is very informative.

      I have applied the same for my interface where i need to do upsert and delete.

      I have applied the same logic as you mentioned in this blog butĀ I am facing one issue while sending the date field.Its basically the conversion issue.

      the format i am sending the date field in the stored procedure : Record.item.value('(CHANGE_DATE)[1]', 'datetime') as CHANGE_DATEĀ 

      error : CallableStatementCallback; uncategorized SQLException; SQL state [S0001]; error code [241]; Conversion failed when converting date and/or time from character string.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.

      Regards,

      Rutuja

      Author's profile photo Junwoo Park
      Junwoo Park

      Hi Yee,

       

      I take your course in Udemy and I upload QnA.

       

      Recently, there seems to have been a change to the MS SQL connection using JDBC in CLOUD CONNECTOR.

      the JDBC environment settings are different and I'm still getting the error.

      Please check and answer my question

       

      Best Regards

      Leo