How to process multiple XML elements in one XML file

Question

I have a XML message file which i want to add to SQL table. XML file has 20 elements ( records ). Everything works file, but only firs record from XML gets to SQL table.

The question is how to put all records to SQL table. ( I guess  I need to create a loop )  and where to put this script.

 

Thanks

Michael

Answer 1

Use message.getAllNodes();  This will return a String array that you can iterate through.  So if your message looks like this:

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

<bookstore>

   <book>

      <title lang="eng">Harry Potter</title>

      <price>29.99</price>

   </book>

   <book>

      <title lang="eng">Learning XML</title>

      <price>39.95</price>

   </book>

</bookstore>

 

You could loop through the books like this:

 

var books = message.getAllNodes('/bookstore/book');

for (var i = 0; i < books.length; i++) {

   var bookMessage = qie.parseXMLString(books[i]);

   qie.info(bookMessage.getNode('//title') + ' costs this much: $' + bookMessage.getNode('//price'));

   var title = bookMessage.getNode('//title');

   var price = bookMessage.getNode('//price');

   var query = "INSERT INTO BOOKS " +

               "(title, price) " +

               "VALUES('" + qie.escapeSql(title) + "', " + qie.escapeSql(price) + ")";

   qie.doQuery('testDB', query, false);

}

So your channel might look something like this:

Comments

This is great. Thank you very much.

Michael

---

You are welcome.  Hope it helps.