Building XML Hierarchies

Example 8. Sort-merge Join

Using the sort-merge algorithm, the inner and outer FLWOR expressions are each translated into a SQL query, sorted by row id, and the results are merged in the XQuery engine.

XQuery

for $u in collection("USERS")/USERS
return
  <user name="{$u/LASTNAME}">{
    for $h in collection("HOLDINGS")/HOLDINGS
    where $h/USERID = $u/USERID
    return
      <holding id="{$h/STOCKTICKER}">{data($h/SHARES)}</holding>
  }</user>

SQL (for users)

SELECT ALL
  nrm5."LASTNAME" AS RACOL1,
  nrm5."ROWID" AS RACOL2
FROM
  "PEPPINO"."USERS" nrm5
ORDER BY
  nrm5."ROWID" ASC

SQL (for holdings)

SELECT ALL
  nrm5."ROWID" AS RACOL2,
  nrm9."STOCKTICKER" AS RACOL3,
  nrm9."SHARES" AS RACOL4
FROM
  "PEPPINO"."USERS" nrm5,
  "PEPPINO"."HOLDINGS" nrm9
WHERE
  nrm9."USERID"  = nrm5."USERID" AND
  LENGTH(nrm9."USERID") = LENGTH(nrm5."USERID")
ORDER BY
  nrm5."ROWID" ASC

Example 9. Outer Join

This example shows how to use a declaration option to tell DataDirect XQuery® to use the outer join algorithm when generating SQL for building hierarchies.

XQuery

declare option ddtek:sql-rewrite-algorithm "outer-join";

for $u in collection("USERS")/USERS
return
  <user name="{$u/LASTNAME}">{
    for $h in collection  ("HOLDINGS")/HOLDINGS
    where $h/USERID = $u/USERID
    return
      <holding id="{$h/STOCKTICKER}">{data($h/SHARES)}</holding>
  }</user>

Generated SQL

SELECT ALL
  RAREL3.RACOL3 AS RACOL3,
  RAREL3.RACOL4 AS RACOL4,
  RAREL3.RAOJ1 AS RAOJ1,
  nrm5."LASTNAME" AS RACOL1,
  nrm5."ROWID" AS RACOL2
FROM
  {oj
    "PEPPINO"."USERS" nrm5
  LEFT OUTER JOIN
    (SELECT ALL
      nrm9."STOCKTICKER" AS RACOL3,
      nrm9."SHARES" AS RACOL4,
      nrm9."USERID" AS USERID,
      2 AS RAOJ1
     FROM "PEPPINO"."HOLDINGS" nrm9) RAREL3
  ON
    RAREL3.USERID = nrm5."USERID" AND
    LENGTH (RAREL3.USERID) = LENGTH(nrm5."USERID")} ORDER BY RACOL2 ASC