Technical Articles
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.
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
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
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!
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.
Hi Yee Loon
That's very helpful information! Exactly what I needed. Excellent work.
Thank you so much,
Philippe
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.
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.
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
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