How to Create JDBC structure in SAP PI (XI&PI 7.0) automatically using AltovaXMLspy

[Senthilprakash1]

For long i have been working on Integration of databases using PI 7.0 system. One major time consuming activity which always i encounter is creating data type structure for DB tables in XI/PI manually.

Things to remember:

1. The field name should be accurate and in some cases Case sensitive.
2. Should not miss out any fields during creating the table structure manually.
3. and most of all recheck the data type created and compare the same with the table structure.

Imagine yourself doing this activity back to back for multiple tables with around 50~100 fields on an avg  in them using SAP XI3.0 or PI 7.0?

Well laziness is the mother of inventions J. We can do this using Altova XMLspy in a jiffy and import the same as XSD into PI system.

I have been using this method for long time now. But now got time to post it :smile: .

In the example:

• We will be connecting to a SQL DB (this procedure is same for any other DB as well).
• Extract the Structure into Altova XMLspy.
• Convert the Structure to required format (Sender or receiver) for SAP PI.
• Export the new structure as XSD into PI and use it :smile: .

Let us assume that we have a simple File to JDBC scenario where in the table contains 40fields in it. And we have to create below Receiver JDBC structure with ACTION = INSERT.

Manual Data structure creation:

Lets see how we can get this entire structure automatically using ALtova XML SPY and use it in PI.

STEP1:

Open Altova XML SPY and navigate to Convert -> Create XML Schema from DB Structure

Then select your DB and provide the authentication details and connect to the DB. (for this example I am connecting to MS SQL) as shown below.

Once connected to the DB. Navigate to the table (in here table name is SAPSRM) and click Export.

STEP 2:

Once done you will be able to see below structure in Altova XML SPY.

Note: go to Schema/WSDL view in bottom for this view.

Above will give you the Table Name (in this case its SAPSRM) and all the fields with its properties.

STEP3:

Now edit the structure and change it to as in fig 1 in STEP1.

This can be achieved by (right clicking on the nodes) and inserting additional nodes and attributes as required.

(Now I will not be detailing on these things, I will leave it up to you to play aroundJ).

Once paying around is done. you can easily make a structure as below, which is the required structure for JDBC receiver data type with field properties. – which we are trying to create.

save this new structure as XSD in local system.

STEP4:

Import the saved XSD into External Definition in SAP PI.

Thus you have it!!!!!!!

This is a error free quick way to create table structures in SAP PI with version 7.0 and below.

thoughts appreciated...

-Senthilprakash Selvaraj SAP PI Flextronics