Skip to Content

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 🙂 .

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 🙂 .

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:

/wp-content/uploads/2012/04/1_92443.png

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

/wp-content/uploads/2012/04/2_92453.jpg


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.

/wp-content/uploads/2012/04/3_92454.jpg

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

/wp-content/uploads/2012/04/4_92455.jpg

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.

/wp-content/uploads/2012/04/5_92456.jpg

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).

/wp-content/uploads/2012/04/6_92457.jpg

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.

/wp-content/uploads/2012/04/7_92458.jpg

save this new structure as XSD in local system.

STEP4:

Import the saved XSD into External Definition in SAP PI.

/wp-content/uploads/2012/04/8_92459.jpg

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


To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

    1. Gaetano Passalacqua

      Hello interesting tool. I have a question if I need to make a selection of fields from different tables I can use a view? and if AltovaXMLspy tool can read a view? Many thanks for your comments

      (0) 
  1. KARUNAKAR ADAPA

    Hi, when i am creating JDBC structure using altova xmlspy  table name creating problem in structure how can i select  filed of the table without table table.
    please see the attached screen shot. Thank you for help.

    (0) 

Leave a Reply