select 
  xmlroot(
    xmlelement(
      "table",
      xmlconcat(
        xmltype(
'<tr><th>DEPTNO</th><th>DNAME</th><th>LOC</th></tr>'),
        xmlagg(column_value)
      )
    ),version '1.0'
  )
from 
  xmltable('
    for $f in ora:view("LSC_DEPT") 
    return 
      <tr>
        <td>{$f/ROW/DEPTNO/text()}</td> 
        <td>{$f/ROW/DNAME/text()}</td> 
        <td>{$f/ROW/LOC/text()}</td>
      </tr>');

XMLROOT(XMLELEMENT("TABLE",XMLCONCAT(XMLTYPE('<TR>
--------------------------------------------------
<?xml version="1.0"?>                             
<table>                                           
  <tr>                                            
    <th>DEPTNO</th>                               
    <th>DNAME</th>                                
    <th>LOC</th>                                  
  </tr>                                           
  <tr>                                            
    <td>10</td>                                   
    <td>ACCOUNTING</td>                           
    <td>NEW YORK</td>                             
  </tr>                                           
  <tr>                                            
    <td>20</td>                                   
    <td>RESEARCH</td>                             
    <td>DALLAS</td>                               
  </tr>                                           
  <tr>                                            
    <td>30</td>                                   
    <td>SALES</td>                                
    <td>CHICAGO</td>                              
  </tr>                                           
  <tr>                                            
    <td>40</td>                                   
    <td>OPERATIONS</td>                           
    <td>BOSTON</td>                               
  </tr>                                           
</table>                                          

3 thoughts on “select in HTML format, XQUERY variant”

1. felipe alberto January 28, 2009 at 20:14

   nice, but if you need format the date, how to made?

   {to_char($f/ROW/DATA_COLUMN/text())} ??
2. Laurent Schneider January 29, 2009 at 13:33

   If you are really hooked on xquery, you may want to use xquery functions

   
   drop table lsc_t;
   create table lsc_t as (select 1 x, sysdate y from dual);
   select 
     xmlroot(
       xmlelement(
         "table",
         xmlconcat(
           xmltype(
   '<tr><th>X</th><th>Y</th></tr>'),
           xmlagg(column_value)
         )
       ),version '1.0'
     )
   from 
     xmltable('
       for $f in ora:view("LSC_T") 
       return 
         <tr>
           <td>{$f/ROW/X/text()}</td> 
           <td>{fn:day-from-date($f/ROW/Y/text())}/{fn:month-from-date($f/ROW/Y/text())}/{fn:year-from-date($f/ROW/Y/text())}</td>
         </tr>');
   XMLROOT(XMLELEMENT("TABLE",XMLCONCAT(XMLTYPE('<TR>
   --------------------------------------------------
   <?xml version="1.0"?>                             
   <table>                                           
     <tr>                                            
       <th>X</th>                                    
       <th>Y</th>                                    
     </tr>                                           
     <tr>                                            
       <td>1</td>                                    
       <td>29/1/2009</td>                            
     </tr>                                           
   </table>                                          
                    
   
3. Laurent Schneider Post authorJanuary 29, 2009 at 13:41

   Otherwise possible is to use xmlagg…
   

   
   select 
     xmlroot(
       xmlelement(
         "table",
         xmlconcat(
           xmltype(
   '<tr><th>X</th><th>Y</th></tr>'),
           xmlagg(
             xmlelement(
               tr,
               xmlconcat(
                 xmlelement(td,x),
                 xmlelement(td,to_char(y,'DD/MM/YYYY'))
               )
             )
           )
         )
       ),version '1.0'
     )
   from
     lsc_t;