select in HTML format, XQUERY variant

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>                                          

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

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

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

  2. 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. 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;
    

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>