XQuery Example 2

The following example returns a parent-child XML structure containing all destinations to which a person has travelled.

SELECT to_clob(

rdbms.xmltype.getclobval(

rdbms.XMLQuery(

LITERAL ' ''<Tours>

{for $pe in ora:view("PERSON")

ORDER BY $pe/ROW/FIRST_NAME

RETURN <Person first_name="{$pe/ROW/FIRST_NAME}" family_name="{$pe/ROW/FAMILY_NAME}">

{

for $pa in ora:view("PARTICIPANT"), $r in ora:view("RESERVATION"), $s in ora:view("SCHEDTOUR")

WHERE $pe/ROW/PERSON_ID = $pa/ROW/PERSON_ID and $pa/ROW/RES_ID = $r/ROW/RES_ID AND $r/ROW/SCHEDTOUR_ID = $s/ROW/SCHEDTOUR_ID

RETURN <Destination>{$s/ROW/DESTINATION/text()}</Destination>

}

</Person>}

</Tours>''  PASSING SYSDATE AS "s" RETURNING CONTENT'

)

)

)

The result is

<Tours>

  <Person first_name="Albert" family_name="SMITH">

    <Destination>EUROPE</Destination>

    <Destination>AUSTRALIA</Destination>

    <Destination>HOLLAND</Destination>

  </Person>

  <Person first_name="Brigitte" family_name="FISHER-SMITH">

    <Destination>HOLLAND</Destination>

    <Destination>AUSTRALIA</Destination>

  </Person>

</Tours>