Cant able to understand the below query ,any one explain?

Question

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;

I know it is giving the total count of row of each table . But what to know how it working ?

score 5 · Answer 1 · answered Nov 12 '21 at 12:34

Examining the query starting from innermost part is best way to understand:

'select count(*) c from ' || table_name

creates a string containing query selecting the number of records from the table referred to by table_name, so if table_name contains xyz the query will be select count(*) from xyz.

xmltype(dbms_xmlgen.getxml(<query>))

executes the dynamically generated query, producing an XML result.

to_number(extractvalue(<xml>, '/ROWSET/ROW/C'))

fetches a specific value from the XML generated before, following a specific path. We need to assume the XML looks like<ROWSET><ROW><C>value</C></ROW></ROWSET>. The extracted value, yet a string, is then converted to a number.

select table_name, <number> count from user_tables order by table_name

is what finally remains...

Alex Poole · Answer 2 · 2021-11-12T15:09:22.680

Break it down:

'select count(*) c from '||table_name gives you a string containing a query against a specific table (from user_tables);
dbms_xmlgen.getxml('select count(*) c from '||table_name) uses the dbms_xmlgen package to run that dynamic query string and returns the result as an XML document, but as a CLOB;
xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)) converts that CLOB to actual XML;
extractvalue(..., '/ROWSET/ROW/C') extracts the C node value from that XML (there is only one per document, and there is one document per table), as a string;
to_number(...) just converts that string to a number.

db<>fiddle with three dummy tables, showing the intermediate steps.

However, the version you have seems to have originated with Laurent Schneider in 2007, and things have moved on a bit since then. The extractvalue() function is deprecated so you should use XMLQuery instead, and you can skip a step by using getxmltype() instead of xmltype(getxml()):

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;

Or (as @Padders mentioned) you could use XMLTable, with a CTE or inline view to provide the XML; which perhaps makes this example a bit more obscure, but is useful if you have more than one value to extract:

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;

The principal is the same though, and I've included those versions in an expanded db<>fiddle.

(Incidentally, I'm not advocating using a keyword like count as a column alias - it's better to avoid those; I'm just sticking with the alais from your original query.)

Possibly worth noting that XMLTABLE would be another option here and could be useful for dynamic SQLs returning more than one column. — Padders, Nov 12 '21 at 13:05

Cant able to understand the below query ,any one explain?

2 Answers2