select in HTML format, XQUERY variant

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

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

DEPTNO DNAME LOC

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

{$f/ROW/DEPTNO/text()} {$f/ROW/DNAME/text()} {$f/ROW/LOC/text()}

');

XMLROOT(XMLELEMENT("TABLE",XMLCONCAT(XMLTYPE('
--------------------------------------------------

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

3 Replies to “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(
    '

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

    --------------------------------------------------
    < ?xml version="1.0"?>

    X Y
    1 29/1/2009

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

Leave a Reply

Your email address will not be published.