How to extract XML data in to SQL db ?

[Former Member]

Hello,

I have 2 queries,

1. I am receiving an XML file from PI in to MII 12.2 which I need to write/insert in to SQL table. I want to use [Param.1] [Param.2] etc to do so.

I have a table created with 5 colums in db. My XML contains 3 rows and 5 data elements - how to use repeater/switch to assign these values to run time params of SQL query which has insert in to statement ?

2. Can MII receive and process the Custom Idocs/Z Idocs from ECC the way it receives and understand standard IDOCS like MATMAS,LOIPRO etc.

Many Thanks in advance for your help !

Best Regards

Ankit

[HariCS23 · Accepted solution]

Ankit,

1. You can execute your SQL query in a loop or send the whole XML to the SP which parses the xml and inserts records.

I would recommend second approach of sending whole xml to SP if you are inserting large chunks of data.

2. Yes, It is possible to receive any idoc type. What all you need to to configure message processing rule and write transaction to insert that data into your DB.

Thanks

Hari

[Former Member · Accepted solution]

Hari,

Thanks a lot   I realised It was easy.

Though, if you cn share some doc abt the bulk update using SP, would be great.

Regards

Ankit Agnihotri

[Former Member · Accepted solution]

Hi Ankit

Below Attached is a simple SP in SQL Server to perform the Bulk insert using the XML sent from SAP MII. [ The Reply editor did not allow me to paste the SQL]

Once the SP is ready, call that from MII and Pass the XML to [Param.1].

Hope this helps.

Suraj

BulkInsertXMLDataIntoSQLTable.txt.zip

[HariCS23 · Accepted solution]

I appreciate Suraj sharing sample/pseudo code snippets.

Thanks

Hari

[Former Member]

Thank you, Hari and suraj for your valuable inputs and making SDN a thirst quencher for knowledge seekers !

cheers