XQuery for the SQL programmer – Using parameters

In Today’s post of the XQuery for the SQL programmer series, we’ll discuss parameterized queries.

A SQL statement can include parameter markers, indicating values that your application will specify at execution time. Consider a SQL statement retrieving all items with a specific description,

[cc lang="sql"]select * from ITEMS where DESCRIPTION = ?[/cc]

XQuery has the concept of external variables, and as it is a strongly typed language, it allows to declare the type of those external variables. The SQL statement above retrieves all items with a specific description. As the description is a string, we declare in the equivalent XQuery below, our external variable as xs:string.

[cc lang="xquery"]declare variable $desc as xs:string external; for $item in collection("ITEMS")/ITEMS where $item/DESCRIPTION = $desc return $item[/cc]

Where in SQL, parameters are mainly used to bind atomic values, with XQuery one can easily bind a complete XML document. This allows to combine XML documents with your data residing in a relational database. Suppose we have an XML document listing various item descriptions and want to retrieve the matching items for all of those descriptions. In XQuery this can be achieved as follows,

[cc lang="xquery"]declare variable $doc as document-node() external; for $desc in $doc/descriptions/description/text() for $item in collection("ITEMS")/ITEMS where $item/DESCRIPTION = $desc return $item[/cc]As we have explicitly declared the variables in in our queries above as external, you might ask if there are other than external variables? Yes, variables can also be declared as not external, in which case the declaration includes an initializing expression. Assume a query where we want to retrieve both the items posted, as well as all the bits from the user with id “U07”. As we need to userid twice in our query, we use a variable which initializes our userid only once, and if needed can change it at this central location.

[cc lang="xquery"]declare variable $userid as xs:string := "U07"; for $item in collection("ITEMS")/ITEMS where $item/OFFERED_BY = $userid return $item , for $bid in collection("BIDS")/BIDS where $bid/USERID = $userid return $bid[/cc]

Starting from our SQL experience, we have now learned over the last months how to write XQuery. But wait... Why would you do so? Why not staying with good old SQL, rather than learning a new query language? For sure we’re not advocating to abandon SQL, but on the other hand, there are scenarios where XQuery is more powerful and increasing productivity. In our next post of the XQuery for the SQL programmer series, we’ll list a number of those typical XQuery use cases.