SELECT *
FROM  XMLTable(
   'for $coc in ora:view("CHAMBER_OF_COMMERCE"),
        $cc in ora:view("COUNTY_CENSUS")
    let $coc_county := $coc/ROW/COC_COUNTY/text(),
        $coc_name := $coc/ROW/COC_NAME,
        $coc_phone := $coc/ROW/COC_PHONE/text(),
        $cc_county := $cc/ROW/CC_COUNTY/text()
    where $coc_county = $cc_county
    return
       <county>
          <name>{$coc_county}</name>
          <chamber phone="{$coc_phone}">{$coc_name/text()}</chamber>
          <attractions>
             {for $a in collection("/public")
              where $coc_county = $a/attraction/county/text()
              return $a
             }
          </attractions>
       </county>
');

COLUMN_VALUE
-----------------------------------------------------------
<county>
  <name>Houghton</name>
  <chamber phone="(906) 337-4579">Keweenaw Convention & Visitors Bureau</chamber>
  <attractions>
    <attraction url="http://www.nps.gov/isro/">
      <name>Isle Royale National Park</name>
      <phones>
        <phone>
          <name>Voice</name>
          <number>(906) 482-0984</number>
        </phone>
        <phone>
          <name>Fax</name>
          <number>(906) 487-7170</number>
        </phone>
      </phones>
      <county>Houghton</county>
    </attraction>
  <attraction url="http://www.museum.mtu.edu/">
      <name>A. E. Seaman Mineral Museum</name>
      <phones>
        <phone>
          <name>Museum</name>
          <number>(906) 487-2572</number>
        </phone>
      </phones>
      <county>Houghton</county>
    </attraction>
  </attractions>
</county>