cancel
Showing results for 
Search instead for 
Did you mean: 

Converting data from XML format to SQL Format

Former Member
0 Kudos

Hi All

This may sound very basic but i need help with the following :

I have a BLS transaction which has to read data from ECC using JCO connections and then store it in a SQL database.

Can some body please tell me how can i convert the data that i obtain from JCO in the form of XML into a form which can be inserted into the SQL database.

Please help.

Regards

Shweta

Accepted Solutions (1)

Accepted Solutions (1)

former_member204240
Active Participant
0 Kudos

Hi Shwetha,

Your flow in BLS Trx goes like this.

JCO action Block

Condition Block(To check if JCO call is success)

Repeater(To get each row of the Jco output)

Insert Qry Call( Your insert query to insert into SQL db).

Just pass each column value((whatever you need) of repeater to the insert query params.

So it just maps all rows values as you are using repeater which is nothing but a loop.

Hope this helps.

Regards,

Rao

Answers (3)

Answers (3)

Former Member
0 Kudos

Thanks all...will try this

Former Member
0 Kudos

Hi Shweta,

Loop at a repeater to the output parameters(Export/Tables) of the JCo Interface/ Function action block.

Then, add a sql query action block(with insert statement), below it and map the repeater row to the respective parameters inSQL Query action block param1, param2, etc

This will insert output rows one after other.

Regards,

Prashant

Former Member
0 Kudos

Hi Shweta,

The tags (e.g <DateTime>,<Value>) in XML become columns in SQL. To insert data to your SQL DB from XML, you need to map these tag values to the respective columns of your SQL DB table.

Please follow the steps below:

1.Write a command SQL query to insert data to your DB table. (e.g. if your table has two columns then in the VALUES part of your insert query you should give '[Param.1]' and '[Param.2]')

2. In a transaction, repeat on each row of your XML using a REPEATER action block and XPATH as /Rowsets/Rowset/Row (if your XML is in standard MII format else as per your XML structure)

3. Map the output of the repeater (e.g Repeater_0.Output{/Row/DateTime} to Param.1 of SQL query action block and Repeater_0.Output{/Row/Value} to Param.2)

In this way you should be able to insert the XML data to your SQL database.

Hope this helps!!

Best Regards,

Anuj