Home:Learn XQuery:White Papers:Generating SQL:Relaxing XQuery Semantics

Relaxing XQuery Semantics

When translating XQuery to SQL, DataDirect XQuery® is careful to preserve XQuery semantics, even if this complicates the generated SQL. For example, unlike XQuery string comparison, SQL character comparison is not sensitive to differences in trailing spaces. To accommodate this semantic difference, the SQL statements that DataDirect XQuery® executes compare both the strings and the length of the strings.

for $u in collection('USERS')/USERS
where $u/FIRSTNAME = 'John'
return <user name="{$u/LASTNAME}"/>

SELECT ALL
  nrm5."LASTNAME" AS RACOL1
FROM
  "PEPPINO"."USERS" nrm5
WHERE
  nrm5."FIRSTNAME" = 'John' AND
  LENGTH(nrm5."FIRSTNAME") = LENGTH('John')
ORDER BY
  nrm5."ROWID" ASC

Note the additional comparison on the string length, which is needed to fully conform to the XQuery specification. But in most cases VARCHAR columns don't have trailing spaces, and trailing spaces in a CHAR column are not significant. Using a declaration option, you can instruct DataDirect XQuery® to ignore the trailing spaces for character comparisons, which corresponds to the semantics of SQL string comparisons, and thus simplifies the generated SQL.

declare option ddtek:sql-options "ignore-trailing-spaces=yes";
for $u in collection('USERS')/USERS
where $u/FIRSTNAME = 'John'
return <user name="{$u/LASTNAME}"/>

SELECT ALL
  nrm5."LASTNAME" AS RACOL1
FROM
  "PEPPINO"."USERS" nrm5
WHERE
  nrm5."FIRSTNAME" = 'John'

The DataDirect XQuery® User's Guide lists more declaration options that can be used to simplify the generated SQL.