Populate Local XML File, and Loop through it

[Former Member]

I created a local property of type XML. I want to populate this variable with the results of a SQL query, and store it in local memory. Then later, I will loop through this file and perform actions. Basically I am going to have to loop through this resultset many times, and if I can store it in memory, I won't have to re-qurey the DB>

#1 Is this possible?

Right now, I use a repeater on the SQL, and assign the rowset/rowset/row to the local XML Variable.(Assign XML). SHould I use Assign XML, or APpend XML for the Link Type

THen later I am trying a repeater to loop through the flle, and I have no idea where to point the xpath expression to.

Thanks for the help in advance,

JEff

[Former Member · Accepted solution]

From what I understand you are trying to do - Rather than repeating on the SQL query you can link the "Result" of the SQL Query action to the local property (type xml) [and yes you just say Assign XML instead of Append or anything else].

What this would do is assign the local variable the results of the SQL Query in an xMII XML format and then you can further work on this XML.

But rather than using the local variable you can repeat on the SQL Query Action (the way you are doing currently) and manipulate what ever you want to. There is no question of requerying the DB as ur DB would be queried only one time (the SQL Query Action Block will ensure this) and then the repeater would be repeating only on the resulset rendered by the SQL Query action.

Did this make it clearer or have I not understood your problem?

[Former Member · Accepted solution]

So your saying I can do it either way(local variable vs repeating SQL action), but you recommend re-using the SQL Query Action?

So I would just create the SQLQuery at the top, then have several repeaters pointed to the SQL throughout my BLT, and it will only query the DB on the first repater/iteration?

Thanks for your help, only my 2nd day on XMII, coming from .NET C#.

[Former Member · Accepted solution]

You got it right my friend , just the SQL Action would suffice.

Wishing you many more happy days on xMII. All the Best!

[Former Member]

Hi, Jeff.

When you add a SQLQuery action to your business logic services, you automatically get an "in memory" version of the results in type XML as the output from the SQLQuery action. There's generally no reason to copy it to a Local XML parameter.

Add the SQLQuery action at the top of your BLS transaction, and the database will only be queried once. You can use any number of repeaters or other actions to act up that data.

Help us better understand the specifics of the functionality you're trying to achieve, and we can provide some more guidance.

Rick

[Former Member]

Rick,

thanks for the response. So if I understand you correctly, once I run the query, if I loop through the result set several differnent times, it only executes the query against the DB the first time, and the next iterations are in memory only?

So its like a disconnected recordset/.NET Dataset.

If I understand you correctly, then I don't need to store it as a local variable.

thanks,

Jeff

[Former Member]

Oops! I guess Rick answered that already.

Yes you actually don't require a local variable the SQL Action takes care of that.