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,

select *
from ITEMS
where DESCRIPTION = ?

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.

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

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,

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

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.

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

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.

Tech Tags: XQuery SQL

Customer Showcase Webinar: DataFlux - Quality is #1

Interested in hearing from a customer of ours how they use DataDirect XQuery and DataDirect XML Converters? And why? Maybe you have a similar need and DataDirect's XML products can help you.

Join our upcoming webinar on September 30th, Tuesday at 11:00am EST, and hear the DataFlux story - presented by Scott Gidley, co-founder and CTO of DataFlux. He will discuss how incorporating DataDirect's XML-based products into their dfPower Studio and DataFlux Integration Server has allowed them to quickly expand their markets by providing support for non-relational formats and industry vertical standards such as HL7 & ACORD.

Register Here before Tuesday, 9/30/08 11:00 AM EST.

How to split a large XML document in many smaller ones?

We often receive this kind of question:
"I have a large XML document and I need to split it in many smaller documents; my document looks like this:<br /><records><br /> <record id="1"><br /> <field>field1.1</field><br /> </record><br /> <record id="2"><br /> <field>field2.1</field><br /> </record><br /> <!-- more of the same --><br /></records><br />...and I need to documents that don't contain more than N records each."
Using DataDirect XQuery this task is quite simple; leveraging the ddtek:serialize-to-url() fuction, you can do something like this:

declare variable $recordsPerDocument := 10;

let $records := doc("c:/books.xml")/records
let $groupCount := xs:integer(fn:ceiling(count($records) div $recordsPerDocument))
for $g in 1 to $groupCount
let $group := $records[fn:position() gt ($g - 1) * $recordsPerDocument and fn:position() le $g * $recordsPerDocument]
return 
 ddtek:serialize-to-url(
  <records>{ $group }</records>,
  concat("file:///c:/split-", $g, ".xml"), "indent=yes")

What if you want to do something similar, but applied to RDBMS tables? How do I split the content of a RDBMS table across multiple XML documents?
Well, as we are talking about DataDirect XQuery, it shouldn't surprise you that basically the same XQuery can be applied to a table:

declare variable $recordsPerDocument := 10;

let $records := collection("myTable")/myTable
let $groupCount := xs:integer(fn:ceiling(count($records) div $recordsPerDocument))
for $g in 1 to $groupCount
let $group := $records[fn:position() gt ($g - 1) * $recordsPerDocument and fn:position() le $g * $recordsPerDocument]
return 
 ddtek:serialize-to-url(
  <records>{ $group }</records>,
  concat("file:///c:/split-", $g, ".xml"), "indent=yes")

Once again XQuery offers a simple, flexible solution for a problem that comes up pretty frequently.

XQuery Data Services workshop

Permit me some promotion for another XQuery workshop.

This interactive workshop introduces an approach to data services based on XML and XQuery and is based on a typical scenario where a hypothetical IT organization needs to create a set of data services exposing various functionality. The workshop covers:

• Transforming XML documents into user-defined formats
• Aggregating XML documents with relational data
• Consuming Web services in XQuery
• Exposing XQuery as data services
• Integrating with non-XML B2B standards, like EDI
• Adhering to XML industry standards, such as ACORD for insurance

When and Where?

• 16th Sept - Paris
• 23rd Sept - Tel Aviv
• 25th Sept – Dublin
• 14th Oct – Johannesburg
• 16th Oct – Cape Town

Register here and win a Nintendo Wii!

XQuery for the SQL programmer – Updating your database

In today’s post we’ll show how to update your relational database using XQuery. As for all posts in this series we’ll start with SQL statements, and show the equivalent xqueries.

The XQuery Update Facility is an upcoming W3C standard, currently in Candidate Recommendation status. The XQuery Update Facility extends XQuery 1.0 and allows to change XML. However, today we will not get into the details of this upcoming standard. We will however show how XQuery allows you to update your RDBMS, in a similar way as SQL does, using the DataDirect XQuery update functionality.

Basically, DataDirect XQuery introduces 3 new XQuery functions,

• ddtek:sql-insert
• ddtek:sql-update
• ddtek:sql-delete

You probably already see the analogy with SQL’s INSERT, UPDATE and DELETE statements, time for some examples.

Inserting rows

The following SQL statement creates a new user, Joe Roxy,

insert into USERS(USERID, NAME) values ('U10', 'Joe Roxy')

The XQuery variant is very similar,

ddtek:sql-insert("USERS", "USERID", "U10", "NAME", "Joe Roxy")

The first argument identifies the table which we want to insert into. Next ddtek:sql-insert has a variable list of column-value pairs.

A "insert into select" is a bit more verbose in XQuery than it is in SQL.

insert into NEWUSERS select * from USERS where NAME LIKE 'Tom%'

In XQuery you would write,

for $user in collection("USERS")/USERS
where starts-with($user/NAME, "Tom")
return
   ddtek:sql-insert("OTHERUSERS",
                    "USERID", $user/USERID,
                    "NAME", $user/NAME,
                    "RATING", $user/RATING)

Deleting rows

Let’s now delete Joe Roxy from the user’s table,

delete from USERS where USERID = 'U10'

With DataDirect XQuery you use ddtek:sql-delete. You select all the users you want to delete, we know how we can do that,

collection("USERS")/USERS[USERID = 'U10']

And now you pass that information as argument to ddtek:sql-delete,

ddtek:sql-delete(collection("USERS")/USERS[USERID = 'U10'])

And of course, if you have a preference for FLWOR expressions, the following is equivalent,

for $u in collection("TESTUSERS")/TESTUSERS
where $u/USERID = 'U10'
return
  ddtek:sql-delete($u)

Updating rows

Let’s now show how to update the item 1001, change its description to Green Bicycle and price = 100,

update ITEMS
set DESCRIPTION = 'Green Bicycle', RESERVE_PRICE = '100'
where ITEMNO = '1001'

Similar to deleting rows, you first of all need to specify which rows to be updated, and pass that as argument to ddtek:sql-update.

for $item in collection("ITEMS")/ITEMS
where $item/ITEMNO = '1001'
return
  ddtek:sql-update($item,
                   "DESCRIPTION", "Green Bicycle",
                   "RESERVE_PRICE", 100)

Just as in SQL, you can update a column with a value computed from the current value. Suppose we want to raise the reserve price for all items with 10%. As shown in the following SQL statement,

UPDATE "xvs"."xvs001"."ITEMS" SET "RESERVE_PRICE" = "RESERVE_PRICE" * 1.10

And in XQuery,

for $item in collection("ITEMS")/ITEMS              
return
  ddtek:sql-update($item, "RESERVE_PRICE", $item/RESERVE_PRICE * 1.10)

Upsert?

Several SQL implementations support upsert or merge functionality. It allows to update a target table with data from a source table. Rows in the target that match the source are updated, and the non existing rows are inserted. XQuery has not such built-in functionality, but there is also no need to. Using a conditional expression, this is easily expressed in XQuery.

Suppose we want to copy all users with rating A from one to another table. If the user exists, we update the row, otherwise we insert.
We iterate over all rows in the USERS table with rating A, and lookup the matching row in the NEWUSERS table.
If a match in NEWUSERS is found, we update the row, otherwise a new row is inserted,

for $source in collection("USERS")/USERS
let $target := collection("NEWUSERS")/NEWUSERS[USERID = $source/USERID] 
where $source/RATING = "A"
return
  if (exists($target)) then
    ddtek:sql-update($target,                  
                     "NAME", $source/NAME,
                     "RATING", $source/RATING)
  else
    ddtek:sql-insert("NEWUSERS",
                     "USERID", $source/USERID,
                     "NAME", $source/NAME,
                     "RATING", $source/RATING)

NULL values

As we learned in The Data Model, in the SQL/XML view, NULL values area represented as missing elements. Similar, to pass NULL to one of the ddtek:sql-* functions, specify the empty sequence.

Setting an item’s description to null in SQL,

update ITEMS
set DESCRIPTION = NULL
where ITEMNO = '1001'

Updating a columns with NULL, is not much different in XQuery,

for $item in collection("ITEMS")/ITEMS
return ddtek:sql-update($item, "DESCRIPTION", ())

All the update examples in this post either use static data (literals) or are about copying data from one to another table. But in most scenarios this data comes out of your application in one or the other way.

With DataDirect XQuery you have the ability to read data out of an XML structure and update your database. Minollo and I have blogged on this subject before in Bulk load of XML data into a relational database and Shredding XML documents into tables, a database independent approach....

As you know in SQL, bind markers facilitates binding values from your application into the SQL statement. What is the equivalent of SQL bind markers in XQuery? Watch out for our next post in the XQuery for the SQL programmer series.

Tech Tags: XQuery SQL

intersection in XQuery

A recent and rather simple question.

I have two sequences, let's say 1,2,3 and 2,3,4 and I need to get the items that exist in both, in this case 2,3

One could think to use the union and intersect operators, available in XQuery. Unfortunately, these operators are based on node identity, the arguments are sequences of nodes not atomic values.

So, there were a few answers of the form $seq1[. = $seq2]
Which is ok if you don’t mind about possible duplicate values in the result.

I would consider using the FunctX XQuery library. It includes a number of set-operation functions.

• functx:value-union
• functx:value-intersect
• functx:value-except

There are of course many more functions in FunctX which will be handy one day or the other during your XQuery programming. And remember DataDirect XQuery has been certified with FunctX. Maybe consider to add the FunctX function library to your XQuery tools set?

Tech Tags: XQuery XQuery+Function+Library FunctX