XML as an input/XML parsing

[Former Member]

Hi All

I am on SAP 12.2 and have a scenario where i am pulling data from ECC and SQL server.

I am getting an Xml from ECC with a coulmn "A" and column "C" like :

<item>

<Rowsets>

<Row>

<A>988</A>

<C> abcd></C>

</Row>

<Row>

<A>1765</A>

<C>def</C>

</Row>

</Rowset>

</Rowsets>

</item>

and data from SQL like

<item>

<Rowsets>

<Row>

<A>1765</A>

<B><2></B>

</Row>

<Row>

<A>988</A>

<B><1></B>

</Row>

</Rowset>

</Rowsets>

</item>

now i need to compare these two excels,look for column A in xml 2 and then combine the two xml's so that i have the final structure like this :

<item>

<Rowsets>

<Row>

<A>988</A>

<B><1></B>

<C>abcd>/C>

</Row>

<Row>

<A>1765</A>

<B><2></B>

<C>def</C>

</Row>

</Rowset>

</Rowsets>

</item>

Can some one please guide how should i be doing the same.I do not want to do the simultaneous calls to ECC and SQL.I want to run the SQL query only once during a day and store its results locally and then compare the two excels once i run the transaction with Jco connection to ECC in order to produce the final result.

[Former Member · Accepted solution]

Hi Shweta,

As per my understanding from your requirement, you can use the below approach for this compare and merging of 2 XML.

Now I am trying to explain it step by step, Hope it will help you.

1. you can create a new XML structure with all the 2 node A,B,C by using SAP MII XML OUTPUT --> Document (I am using this approach because you desired output is in Rowsets/Rowset/Row format ie. standard MII XML Format)

2. Repeat on the ECC xml on row and pick node A value

3. Pass it to the SQL query to check if this value is present in the SQL table or not (as per my understanding you have no need to take the SQL output XML at the beginning before checking)

4. Check if any row returns or not for that particular node A value.

5. if row returns, then put the node A value and Node C value from the ECC XML and Node B value from SQL Output to your defined new XML structure by using Row action block under SAP MII XML OUTPUT.

6. and finally put the final output to any output variable defined in transaction properties using Assignment action block.

If you need any more clarification, please reply.

Regards,

Suman

[Former Member]

Hi Shweta,

Just to add one more point, from my MII experience, there is a least chance to get any XML in Rowsets/Rowset/Row format from ECC because it's the standard SAP MII XML format.

Regards,

Suman

[Former Member]

I think ReferenceDocument Loader and couple of SAP MII XML output functions should do the trick.

Closing the thread

[former_member204240]

Hi Shweta,

You dont need ReferenceDocument Loader, if you already created a table locally to store sql query output. you can use the specific supported datatype to store xml messages and get that on query template.

Which DB are you using?

Just a point to ponder, If you are doing any calculation and want to show that as another column then you can use calculated column action. Don't no what's is actual requirement.

Regards,

Padma