Home:Learn XQuery:XQuery Examples:XQueryWebService:Photo Search:Querying Relational Data

XQuery Source — Querying Relational Data

We've already shown how you can use DataDirect XQuery to update a relational database. This section describes using DataDirect XQuery and the collection() function to retrieve data from a relational database — in this case, the photo search queries that are saved by the Photo Search demonstration.

Displaying Saved Queries

Each time you execute a search of Flickr photos, the query — which includes your user name, the search term, the maximum number of hits, and some other information — is saved to the queries table in the database running on the Web application server. When you click the Show me my queries button, the Photo Search application queries the database for your queries and displays the results, as shown in the following illustration:

Clicking the Show me my queries button invokes the ajax_savedqueries function, which in turn calls savedqueries.xquery. Let's take a look at that XQuery code now.

savedqueries.xquery

As you can see, the savedqueries.xquery uses the collection() function to query the Photo Search database for saved queries:

declare option ddtek:serialize "method=html";
declare variable $login as xs:string external;
<table id="{$login}">
    {
        for $user in collection("users")/users
        where string($user/login) = string($login)
        return
            for $query in collection("queries")/queries
            where $query/user_id = $user/id
            return
                <tr>
                    <td>
                        <a href="null.html" target="block" onclick="SearchInternal('{$login}', '{$query/keywords}', '{$query/maxHits}')">
                            {$query/keywords} (max hits {$query/maxHits})
                            </a>
                    </td>
            </tr>
     }
</table>,
    <iframe name="block" style="display:none"/>

Notice that savedqueries.xquery contains only XQuery — the SQL statements required to connect to and query the relational database are created automatically by DataDirect XQuery. Once a user in the users table whose user_id matches the $login, the record containing the query information (the key words used for the search and the max hits specified) is returned and presented in a table, with each record appearing in its own row.