Nick,
We’ve done something similar at AutoTrader.com to list a huge number of heirarchical choices on the page. Our main bottleneck, though, was the list size, so we had to make a somewhat complex custom control type.
However, if your data size isn’t too large, you can use a simple XML Application in the following manner (essentially what RLJ said, starting on page 259):
In your new XML Application (Workbench->XML Server->Applications) :
[ol]
[li]Create a new “Query Resource” and open it. You should see an image of a pipe with no icons on top of it.
[/li][li]Select your table from the Database Explorer and drag it onto the left side of the pipe. If you don’t have a JDBC connection to your target database, you’ll need to add it.
[/li][li]Insert a new “Selector” and drag it onto the pipe (a little black box should appear on the pipe on the left-most screw-top looking thing if you have it right). the selector icon will snap into place.
[/li][li]Double click on the selector to open it and fill in the query parameters you want to use, then click OK. Personally I usually manually enter the SQL because I find it more granular and easy to manipulate. (Also, sometimes in my install, the display of the typed characters lags a couple keystrokes behind what you type. this can get very frustrating, so if you’re typing the query in, have it built in a text editor and simply paste it in).
[/li][li]Copy the sys_Lookup.dtd from a resource in the sys_ceSupport application and paste it into your new resource’s pipe. Again, drag it around in the window until the little black box appears (on the right of the pipe, this time).
[/li][li]Insert a new Mapper and drag it around in a similar way, then double click on it. Drag 2 table fields (on the left; they can be the same, but don’t have to be) onto the Back-end side of the mapper. Drag “PSXDisplayText” from the DTD (on the right) to the XML column beside the table field you want to use for your label and “Value” to beside the value field. Then click OK and close the pipe editor panel.
[/li][li]Save, close, and restart the XML application.
[/li][/ol]
Back in the Content Editor window:
[ol]
[li]Select your control, and click on “All Properties…”
[/li][li]Click on the “…” button beside the Control field (which should read “sys_DropDownSingle”), then click on the “Choices” tab in the popup.
[/li][li]select “Retrieve from xml application” and click on the “…” button beside the URL field.
[/li][li]choose your Application’s name, and select the resource that you created. If you had variabled in your SQL, you can provite them in the “Params” section.
[/li][/ol]
That should do it.
The query params that you can provide in the control’s URL query can be referenced in a manual SQL statement with the following code: <CODE>:“PSXParam/paramName”</code> - and if you wish to make sure the query doesn’t cause an error when you try to execute it with a missing param, enclose that with single quotes, too… even if you need an integer in your SQL.
I have some code too, that will allow you to populate a custom control with data from an AJAX query that returns JSON-formatted data so that you can chain 4 or 5 controls together to make a drop-down heirarchy that doesn’t take forever and a day to load a huge ammount of data, and samples of how to create such a monster. Let me know if that mess would be helpful as well.
As for retrieving the data in the template, I find it far more direct to use <code>$rx.db.get(‘datasource’,‘sqlQuery’)</code> but to each his own. 
Best Regards,
– Rush
