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>');

DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

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

  1. felipe alberto

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

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

  2. Laurent Schneider

    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(
    '

    X Y

    '),
    xmlagg(column_value)
    )
    ),version '1.0'
    )
    from
    xmltable('
    for $f in ora:view("LSC_T")
    return

    {$f/ROW/X/text()} {fn:day-from-date($f/ROW/Y/text())}/{fn:month-from-date($f/ROW/Y/text())}/{fn:year-from-date($f/ROW/Y/text())}

    ');
    XMLROOT(XMLELEMENT("TABLE",XMLCONCAT(XMLTYPE('

    --------------------------------------------------

    X Y
    1 29/1/2009

  3. Laurent Schneider Post author

    Otherwise possible is to use xmlagg…

    select
    xmlroot(
    xmlelement(
    "table",
    xmlconcat(
    xmltype(
    '

    X Y

    '),
    xmlagg(
    xmlelement(
    tr,
    xmlconcat(
    xmlelement(td,x),
    xmlelement(td,to_char(y,'DD/MM/YYYY'))
    )
    )
    )
    )
    ),version '1.0'
    )
    from
    lsc_t;

Comments are closed.