Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table x1 as select * from dual;
1 rows affected

create table x2 as select * from dual union all select * from dual;
2 rows affected

create table x3 as select table_name from all_tables;
117 rows affected

select table_name,
  'select count(*) c from '||table_name
from user_tables
order by table_name;


TABLE_NAME
'SELECTCOUNT(*)CFROM'||TABLE_NAME


X1
select count(*) c from X1

X2
select count(*) c from X2

X3
select count(*) c from X3


select table_name,
  dbms_xmlgen.getxml('select count(*) c from '||table_name)
from user_tables
order by table_name;


TABLE_NAME
DBMS_XMLGEN.GETXML('SELECTCOUNT(*)CFROM'||TABLE_NAME)


X1
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <C>1</C>
 </ROW>
</ROWSET>


X2
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <C>2</C>
 </ROW>
</ROWSET>


X3
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <C>117</C>
 </ROW>
</ROWSET>



select table_name,
  xmltype(
    dbms_xmlgen.getxml('select count(*) c from '||table_name)
  )
from user_tables
order by table_name;


TABLE_NAME
XMLTYPE(DBMS_XMLGEN.GETXML('SELECTCOUNT(*)CFROM'||TABLE_NAME))


X1
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <C>1</C>
 </ROW>
</ROWSET>


X2
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <C>2</C>
 </ROW>
</ROWSET>


X3
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <C>117</C>
 </ROW>
</ROWSET>



select table_name,
  extractvalue(
    xmltype(
      dbms_xmlgen.getxml('select count(*) c from '||table_name)
    ),
    '/ROWSET/ROW/C'
  )
from user_tables
order by table_name;


TABLE_NAME
EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECTCOUNT(*)CFROM'||TABLE_NAME)),'/ROWSET/ROW/C')


X1
1

X2
2

X3
117


select table_name,
  to_number(
    extractvalue(
      xmltype(
        dbms_xmlgen.getxml('select count(*) c from '||table_name)
      ),
      '/ROWSET/ROW/C'
    )
  ) count
from user_tables
order by table_name;


TABLE_NAME
COUNT


X1
1

X2
2

X3
117


select table_name,
  to_number(
    xmlquery(
      '/ROWSET/ROW/C/text()'
      passing xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name))
      returning content
    )
  ) count
from user_tables
order by table_name;


TABLE_NAME
COUNT


X1
1

X2
2

X3
117


select table_name,
  to_number(
    xmlquery(
      '/ROWSET/ROW/C/text()'
      passing dbms_xmlgen.getxmltype('select count(*) c from '||table_name)
      returning content
    )
  ) count
from user_tables
order by table_name;


TABLE_NAME
COUNT


X1
1

X2
2

X3
117


select t.table_name, x.count
from (
  select table_name,
    dbms_xmlgen.getxmltype('select count(*) c from '||table_name) as xml
  from user_tables
) t
cross apply xmltable (
  '/ROWSET/ROW'
  passing t.xml
  columns count number path 'C'
) x
order by table_name;


TABLE_NAME
COUNT


X1
1

X2
2

X3
117

TABLE_NAME	'SELECTCOUNT(*)CFROM'\|\|TABLE_NAME
X1	select count(*) c from X1
X2	select count(*) c from X2
X3	select count(*) c from X3

TABLE_NAME	DBMS_XMLGEN.GETXML('SELECTCOUNT(*)CFROM'\|\|TABLE_NAME)
X1	<?xml version="1.0"?> <ROWSET> <ROW> <C>1</C> </ROW> </ROWSET>
X2	<?xml version="1.0"?> <ROWSET> <ROW> <C>2</C> </ROW> </ROWSET>
X3	<?xml version="1.0"?> <ROWSET> <ROW> <C>117</C> </ROW> </ROWSET>