Morning,

I presently return XML via SQL for a number of cases in a query, I build the XML and then the XML is fired off to a web service to update our back office system.

At present what I do is write a SQL query to get the list of records I need to generate the XML for, and then have a second SQL query loop through that list and create the XML - very inefficient from the point of view of SQL.

So I figured it'd be easier to obtain the full list and then loop through with VC XML's Read Node. My XML is like the following



What I want to do is be able to loop through and select each xmlqry element and its subsequent data, my example of how I expect the "Full Output" of VisualCron would be from

1 <xmlqry>
2 <name></name>
3 <addr1></addr1>
4 <tel></tel>
5 </xmlqry>
6 <xmlqry>
7 <name></name>
8 <addr1></addr1>
9 <tel></tel>
10 </xmlqry>
11 <xmlqry>
12 <name></name>
13 <addr1></addr1>
14 <tel></tel>
15 </xmlqry>

To This

1 <xmlqry>
<name></name>
<addr1></addr1>
<tel></tel>
</xmlqry>
2 <xmlqry>
<name></name>
<addr1></addr1>
<tel></tel>
</xmlqry>
3 <xmlqry>
<name></name>
<addr1></addr1>
<tel></tel>
</xmlqry>

So it treats each XMLQRY element and its data as a seperate line so that I can loop through and fire them off to the webservice.

The help on this would be appreciated as it will fix a number of performance issues with queries.

Hi,

Would really appreciate if someone can assist here - not sure if its a limitation of VisualCron here.

Cheers,

James

There is no built-in support currently for looping through XML nodes unless you 1. know the element count 2. find an xpath query that can return the element. Because, in theory you could call the XML Read Node with:

/qrylist[1]/xmlqry[1]
/qrylist[1]/xmlqry[2]
/qrylist[1]/xmlqry[3]

So, if you know the number of elements you could create For loop type and pass the current loop number to the xpath query to get one at a time and process it.

---

Henrik,

Thank you for the suggestion, I can infact get the number of elements.

:)