Retrieve multiple rows from a rowset matching a criteria using XPath in SAP MII 12.2.5

[saumya_govil]

Dear Experts,

Need a way to query a local XML type property and get all rows of a rowset that match a criteria. We need to do this without using a loop on a result set because using loops currently is making the transaction hang and the gives a transaction timeout error after some time. Example of what we intend to do: Bookstore_XML is a local property of XML type containing:

<?xml version='1.0'?>
<bookstore>
<book> <title>abc</title> <author>Enid</author> <price>10</price> </book> 
<book> <title>def</title> <author>Enid</author> <price>20</price> </book>
<book> <title>ghi</title> <author>Jone</author> <price>30</price> </book>
<book> <title>jkl</title> <author>Jone</author> <price>40</price> </book>
</bookstore>  

I need to select all books from the bookstore where author = 'Enid'. Output like below:

<?xml version='1.0'?> <bookstore>
  <book> <title>abc</title> <author>Enid</author> <price>10</price> </book>
  <book> <title>def</title> <author>Enid</author> <price>20</price> </book>
  </bookstore> 
 

I try doing like:

Local.Bookstore_XML{//bookstore/book[author='Enid']}

... and assign XML to an XML type property. But I get a blank result.

Any pointers would be highly appreciated.

Regards,

Saumya Govil

[Former Member · Accepted solution]

Hi Saumya,

You can follow the following steps, hope it will help you.

Steps are as follows:

1) Repeat on "book" using repeater.

2) Filter the row based on author using condition block

3) Create a local variable using the below format,

<?xml version="1.0" encoding="UTF-8" standalone="no"?><book>

<title/>

<author/>

<price/>

</book>

4) Assign the value from repeater to that local variable XML

5) Create another local variable using the below format,

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<bookstore/>

6) Now append the book XML to the upper mentioned XML like that,

7) Finally create a Output variable in transaction variable and assign Bookstore XML there.

I hope it will help you, and it will solve your problem. The output you will get like that.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<bookstore>

    <book>

<title>abc</title>

<author>Enid</author>

<price>10</price>

</book>

    <book>

<title>def</title>

<author>Enid</author>

<price>20</price>

</book>

</bookstore>

Regards,

Suman

[saumya_govil · Accepted solution]

Hi Suman,

Thanks a lot for your reply. It seems like you actually spyed through my code...

I am actually doing the same approach to get the result set as I found no other more efficient way!

The problem I face is that after getting the result I have to loop through another XML to get all matching data for those books and further again in another to get further data. So when I do so many nested loops.. the transaction just dies! It hangs for 3-4 mins and after that I get a timeout error.

So, I wanted to know if there is any better and efficient approach that can help me avoid all this looping.

Thanks & Regards,

Saumya Govil

[former_member211944 · Accepted solution]

Hi Saumya,

One way would be to use XSL and transform the XML as mentioned by Anuj.

This could be done by creating XSL and then importing it in MII.

Then use the XSL Transformation Action Block under XML Functions Group.

Use Transform URL as :  WEB://<Project>/<Path>/XSL_File.xsl

Regards,

Rohit Negi.

[former_member211944 · Accepted solution]

Hi Saumya,

I used the logic which you are using(XPath in Assignment Action Block) and I got the xml with only one node of the data and not all the matching nodes.

This is because the Asignment Action Block will not iterate on the nodes found by the XPath but will assign the first matched node.

Thus XSL would be better to use than iterating over the XML using the Iterator.

Refer to the following Link for more information:

If still you want to use the Iteration method then you can refer to the following Link for improving the

performance:

Regards,

Rohit Negi.

[saumya_govil · Accepted solution]

Hi Rohit,

Thanks a lot for the response.

I think you are right. I tried with assignment block but the XPATH assignment fails.

Anyways, I discovered that the issue was because of a data formatting issue when I was using the comparison in the Repeater block. After correcting the same using the format() function the loop works fine.

Also would like to tell that we can do this filtration using the Filer action block in MII.

Yes, using the XSLT is also a very good option and we can use this in case the volume of data is quite high.

Thanks a lot all for your responses. Am closing the thread now.

Regards,

Saumya Govil

[Former Member]

Hi Saumya,

The only way I can think of here is to write one XSL to transform your XML into required XML.

This wont hamper your performance as it is doing if put inside a loop.

Thanks & Regards,

Anuj