Oracle XML Gateway Message Designer is a tool to generate a map file between 2 data structures. The 2 data structures would be a Source and a Target structure. This map will be used within Oracle XML Gateway interfaces and XML Gateway will match the source data it receives with the source data structure provided in the map file. If the source data structure matches with the map source data structure only then XML Gateway commences transforming the file, also defined in the map file, and then XML Gateway comes out with the Target data based on the Target data structure.

Create a table in Oracle database

Create a view on top of this table to be used by XML Gateway

Note: XML Gateway Message Designer is unable to see tables. It can detect views and hence we created a view with the same structure on top of the table.

Open XML Gateway message designer.

Click File > New > Data Definition

Enter Data definition name and description. Select Data Definition type Database

Click Next

Enter connectivity details and click Next

Select the view XX_DATA_V. Note that the table XX_DATA is not visible to XML Gateway Message Designer.

Click on the right button to select the view. Click Next.

Select all the columns and click on the right button. Click on Next

Select view to form levels. Click Next.

Click on Finish.

Now the Data definition is created from the view.

Save the data definition by clicking on File > Save

We have now created the source data definition. This means that we have defined the data structure of the XML file that will be sent to the Oracle Applications.

Click on File > New > Transaction Map. The mapping wizard opens up.

Click Next.

Enter Map name and description. Click Next

We can take the data definition created earlier as the source definition. Instead we shall use an existing DTD for the source XML data as the input to XML Gateway. Hence we shall select new source data definition.

Click next.

Enter the source data definition name and description. Select the data definition type as XML. Click Next.

Select the DTD from your local computer. Enter the required details. We got the run time location of the DTD from Oracle. Click Next.

For target data definition, we shall create a new one from the database. It will be the same structure as the database table so that the data can be easily inserted. Click Next.

Enter details and click Next.

Enter database connectivity details. Click Next.

Select the view XX_DATA_V and click Next

Select all columns and click Next

Select level. Click Next.

Click Finish. The wizard will exit and the TransactionMap window will open up.

Check the source and target definitions. The ItemType on the field level should be properly identified or the mapping will be incorrect.

You can see that the node ITEM and DATA have been identified as Elements. You need to change the ItemType value to Level.

Check the Target Definition tab.

The Target definition looks ok as the Level shows on the correct fields. Click on Level Mapping tab to map the nodes.

Drag and drop the nodes Data and Item on XX_DATA_V.

Now the levels are mapped. In other words the nodes have been mapped. We shall now map the elements contained in the nodes. Click on Element Mapping to map the individual elements.

Drag and drop XX_DATA_V from XX_DAT to XX_DATA_V on XX_TARGET to map the levels. Click on Element Mapping. Drag and drop all the fields from the left on to the corresponding fields on the right.

Let us add some logic into the field BUYERID so that the source value is always added with 10 when it goes to the target. Right click onBUYERID field on the Target and click on Actions.

Select the Add function. Select the BUYERID from the Source and add 10 to it. Click OK. Now we need to ensure that the Target data definition enters the data into the database. Right click the Target node, XX_DATA_V, and select Actions. This node corresponds to the database. table/view.

In the Post Process tab, select “Insert Into Database Table”. Click ok. Now the data is inserted into the database but XML Gateway does not implicitly commit the data. We have to add this also. Right click on the root node of Target, i.e. TARGET_DEFN, and select Actions.

Select Execute Procedure. Select Assign Parameter Value.

Enter the database details and the procedure name. We have created the procedure, XX_COMMIT, to perform the COMMIT on the data. Click OK on both forms to close. Save the mapping as XX_DAT.xgm.

Your mapping file is now ready.

Load the DTD and the map into the database

The source DTD is,

Let us load the source DTD into the database,

FTP the xgm file and the source dtd file to Unix. Load the xgm file into the XML Gateway repository using the following command,

appspwd is the apps database password

eyepdbg1 is the server name

1522 is the database port

PREPATCH is the database SID

You will find a log file named, loader.log. Open the file in vi editor in Unis and the log file will look like the following,

The log file is given below

This means that the mapping file has been loaded successfully.

Let us now load the dtd file

SRC_DATA is the root element of the DTD and ecx/oag62 is the location

Check the loader.log file again and at the end of the log file you will see,

This means the DTD has also been loaded properly.

Now we shall create a procedure XX_COMMIT (that had been called in the map file creation) to commit any transaction coming through XML Gateway into the database as XML Gateway does not implicitly commit.