The important error in what has been reported to you is this one:
ORA-00933: SQL command not properly ended
Remove the semicolon from the query inside the dbms_xmlgen.getxml() call:
select table_name,
column_name,
xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml( 'select ' || column_name || ' from (select ' || column_name ||', rank () over (order by count(*) desc) as rnk from '
|| table_name || ' Group by ' || column_name || ') where rnk = 1'))
-------^ no semicolon here
returning content) as C
from all_tab_columns
...
Your XPath seems to be wrong too though; you're looking for /ROWSET/ROW/C, but C is the column alias for the entire expression, not the column being counted. You need to alias the column name within the query, and use that in the XPath:
select table_name,
column_name,
xmlquery('/ROWSET/ROW/COL/text()'
-- ^^^
passing xmltype(dbms_xmlgen.getxml( 'select ' || column_name || ' as col from (select ' || column_name ||', rank () over (order by count(*) desc) as rnk from '
-- ^^^^^^
|| table_name || ' Group by ' || column_name || ') where rnk = 1'))
returning content) as C
from all_tab_columns
...
With your sample data that gets:
TABLE_NAME COLUMN_NAME C
------------------------------ ------------------------------ ----------
T1 col.1 y
T1 col.2 224510
T1 col.3 m2
T2 col.1 3
T2 col.2 germany
T2 col.3 xxx
db<>fiddle
The XMLQuery is returning an XMLtype result, which your client is apparently showing as (XMLTYPE). You can probably change that behaviour - e.g. in Sql Developer from Tool->Preferences->Database->Advanced->DIsplay XMl Value in Grid. But you can also convert the reult to a string, using getStringVal() to return a varchar2 (or getClobVal() if you have CLOB values, which might cause you other issues):
select table_name,
column_name,
xmlquery('/ROWSET/ROW/COL/text()'
passing xmltype(dbms_xmlgen.getxml( 'select ' || column_name || ' as col from (select ' || column_name ||', rank () over (order by count(*) desc) as rnk from '
|| table_name || ' Group by ' || column_name || ') where rnk = 1'))
returning content).getStringVal() as C
-- ^^^^^^^^^^^^^^^
from all_tab_columns
...
As you can see, this doesn't do quite what you might expect when there are ties due to equal counts - in your example, there are found different values for T1."col.2" (null, 10, 22, 45) which each appear once; and the XMLQuery is sticking them all together in one result. You need to decide what you want to happen in that case; if you only want to see one then you need to specify how to decide to break ties, within the analytic order by clause.
I actually want to see all results but I expected to see them in different rows
An alternative approach that allows that is to use XMLTable instead of XMLQuery:
select table_name, column_name, value
from (
select atc.table_name, atc.column_name, x.value, x.value_count,
rank() over (partition by atc.table_name, atc.column_name
order by x.value_count desc) as rnk
from all_tab_columns atc
cross join xmltable(
'/ROWSET/ROW'
passing xmltype(dbms_xmlgen.getxml(
'select "' || column_name || '" as value, count(*) as value_count '
|| 'from ' || table_name || ' '
|| 'group by "' || column_name || '"'))
columns value varchar2(4000) path 'VALUE',
value_count number path 'VALUE_COUNT'
) x
where atc.owner = user
and atc.table_name in ('T1', 'T2', 'T3', 'T4')
)
where rnk = 1;
The inner query cross-joins all_tab_columns to an XMLTable which does a simpler dbms_xmlgen.get_xml() call to just get every value and its count, extracts the values and counts as relational data from the generated XML, and includes the ranking function as part of that subquery rather than within the XML generation. If you run the subquery on its own you'll see all possibel values and their counts, along with each values' ranking.
The outer query then just filters on the ranking, and shows you the relevant columns from the subquery for the first-ranked result.
db<>fiddle
