Home > sql, xml > select in HTML format, XQUERY variant

select in HTML format, XQUERY variant

November 7th, 2008
Goto comments Leave a comment

Same as Select in html format, but with XMLTABLE instead of XMLTRANSFORM



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>                                          

Bookmark and Share

  1. felipe alberto
    January 28th, 2009 at 20:14 | #1
    Reply | Quote

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

    {to_char($f/ROW/DATA_COLUMN/text())} ??

  2. Laurent Schneider
    January 29th, 2009 at 13:33 | #2
    Reply | Quote

    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
    January 29th, 2009 at 13:41 | #3
    Reply | Quote

    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;

  1. No trackbacks yet.