Mapping JDBC -> IDOC

[Former Member]

Hi,

In my scenario, data is fetched from database and passed on to an SAP R/3 Idoc COSMAS01.

I could figured out, that the main problem is in the mapping.

In my design I have put source structure which is exactly the same as database structure and mapped it to target structure.

Source structure:

MT_COSMAS_JDBC

resultset

row

HKEY

HAC

HUST

...

target structure

COSMAS01

IDOC

Begin

E1CSKSM

Kostl

Land1

....

Graphical MAPPING:

HKEY -> Kostl

HAC -> Land1

......

My first problem is to define the right database structure:

When I configure the JDBC sender adapter with the documentname “resultset”, I become the following xml-structure in the SXMB_MONI:

<?xml version="1.0" encoding="utf-8" ?>

- <resultset>

- <row>

<HKEY>130003328</HKEY>

<HGDATB>99999999</HGDATB>

<HGDATV>20050301</HGDATV>

<HUSRID>MIDDELA</HUSRID>

<HAC />

......

</row>

</resultset>

When I add the documentnamespace: http://xxxxx.de/XI/JDBC/COSMAS, I become the following xml structure in the SXMB_MONI:

<?xml version="1.0" encoding="utf-8" ?>

- <ns:resultset xmlns:ns="http://xxx.de/XI/JDBC/COSMAS">

- <row>

<HKEY>130003328</HKEY>

<HGDATB>99999999</HGDATB>

<HGDATV>20050301</HGDATV>

<HUSRID>MIDDELA</HUSRID>

<HAC />

......

</row>

</ns:resultset>

After this I tested my mapping with both xml structures (upload: xxxx.xml)

The result was, that the mapped fields doesn’t appear in the IDOC structure. Only the constants in my mapping appear in this Idoc structure.

Are there some help for an JDBC -> IDOC Mapping?

If there are multiple rows in the source structure, have I to do XLST Mapping??

Example 2 rows -> creating 2 IDOCS COSMAS01 ??

- <resultset>

- <row>

<HKEY>130003328</HKEY>

<HGDATB>99999999</HGDATB>

<HGDATV>20050301</HGDATV>

<HUSRID>XXXXX</HUSRID>

<HAC />

......

</row>

- <row>

<HKEY>130004444</HKEY>

<HGDATB>99999999</HGDATB>

<HGDATV>20050301</HGDATV>

<HUSRID>XXXXXX</HUSRID>

<HAC />

......

</row>

</resultset>

Thanks

Christoph

[former_member187339 · Accepted solution]

Hi Christoph,

<i>>>When I add the documentnamespace: http://xxxxx.de/XI/JDBC/COSMAS, I become the following xml structure in the SXMB_MONI:</i>

When you configure JDBC as sender you shouldn't specify the namespace in the Message Type for DB.

<i>>>If there are multiple rows in the source structure, have I to do XLST Mapping??</i>

You can go for graphical mapping. Make both data types as 0..unbounded. And map the corresponding roots.

Hope this will be helpful.

Regards

Suraj

[Former Member · Accepted solution]

Hi Suraj,

How should the source structure look like:

MT_COSMAS_JDBC

resultset

row

HKEY

HAC

HUST

...

documentname in JDBC sender : resultset

or

MT_COSMAS_JDBC

row

HKEY

HAC

HUST

...

documentname in JDBC sender : MT_COSMAS_JDBC

>>You can go for graphical mapping. Make both data types as 0..unbounded. And map the corresponding roots<<

I guess I can change the IDOC from 1...1 to 0...unbounded?

Christoph

[former_member187339 · Accepted solution]

Hi Christoph,

This is the correct format.Message Type and Data type name is resultset.

resultset

row

HKEY

HAC

HUST

...

Regards

Suraj

[Former Member · Accepted solution]

If i understand, you want to create more IDOC with the same database resultset.

Given that EACH IDOC structure is a "message" in XI syntax, you cannot set the IDOC structure to 0...unbound.

Instead, you should use a Multi-mapping (that is, in your mapping program set the occurence of the IDOC as 0...unbounded) then map IDOC root to the <row> field in order to manage the occurrency.

Remember that you can use a multi-mapping only inside a BPM

If you don't want to use the BPM, you should structure your select query in order to retrieve only one row: this can be done using the TOP keyword or the ROWNUM field, depending on your DBMS.

Hope this helps,

Alessandro

[Former Member · Accepted solution]

for Idocs there is a simpler solution which has much better performance than using a BPM.

1. Download the IDoc definition as XSD.

2. Change occurrence of node IDOC to 1..unbounded.

3. Upload changed XSD as external definition and

4. use the external definition in the mapping.

With this solution the IDoc Adapter will take care to split the IDoc XML to 1 IDoc per IDOC node. This is far better performance than BPM and as far as i remember it is also what SAP suggests in such cases. Please refer to SAP note 814393 for details.

Best regards

Christine

[Former Member · Accepted solution]

As of SP09 or 10 it is possible to specify document name and document namespace in the JDBC Sender adapter.

Specifying MY_COSMAS_JDBC as document name and http://mycompany.com/my/namespace it would result in a document like:

<ns1:MY_COSMAS_JDBC ns1:xmlns="http://mycompany.com/my/namespace">

<row> </row>

<row> </row>

...

</ns1:MY_COSMAS_JDBC>

you do not necessarily need to put your Message Type within the above mentioned namespace in the Repository, but you need to make sure, that the field XML-Namespace in the Message type is the same, that you use in the Adapter configuration.

Typically this field is filled with the same namespace then the one to which the message type belongs, so if you want to use a different one, you have to manually adjust it.

Best regards

Christine

[Former Member · Accepted solution]

Hi Christine,

Thanks a lot.

The suggestion to change the XSD-IDoc definiton works perfect.

Christoph