Oracle Tips: XMLSequence Example

The XMLSEQUENCE operator is used to split multi-value results from XMLTYPE queries into multiple rows. The XMLSequence has two forms: 

The first form takes as input an XMLType instance and returns a varray of the top-level nodes in the XMLType. 

The second form takes as input a REFCURSOR instance, with an optional instance of the XMLFormat object, and returns as an XMLSequence type an XML document for each row of the cursor. 

Example 1: 

1. SELECT  ROWNUM AS ID  
2.     ,   EXTRACTVALUE(COLUMN_VALUE,'/ROW/DEPTNO') DEPARTMENT_NO  
3.     ,   EXTRACTVALUE(COLUMN_VALUE,'/ROW/DNAME') DEPARTMENT_NAME   
4. FROM TABLE(XMLSEQUENCE(extract(xmltype(DBMS_XMLGEN.GETXML('SELECT * FROM DEPT')),'/ROWSET/ROW'))); 

Output

1. NO    DEPARTMENT_NO            DEPARTMENT_NAME  
2. ------------------------------------------------------------  
3. 1      10                       ACCOUNTING  
4. 2      20                       RESEARCH  
5. 3      30                       SALES  
6. 4      40                       OPERATIONS  

Example 2: 

1. SELECT ROWNUM AS ID,  EXTRACTVALUE(COLUMN_VALUE,'/ROW/DEPTNO') DEPARTMENT_NO,EXTRACTVALUE(COLUMN_VALUE,'/ROW/DNAME')   
2. DEPARTMENT_NAME  
3. FROM  
4.    TABLE(XMLSEQUENCE(CURSOR(SELECT * FROM DEPT)));

Output

1. NO    DEPARTMENT_NO            DEPARTMENT_NAME  
2. ------------------------------------------------------------  
3. 1      10                       ACCOUNTING  
4. 2      20                       RESEARCH  
5. 3      30                       SALES  
6. 4      40                       OPERATIONS  

Example 3: 

1. SELECT rownum AS ID,EXTRACTVALUE(b.COLUMN_VALUE,'/ROW/DEPTNO') DEPARTMENT_NO   
2. , EXTRACTVALUE(b.COLUMN_VALUE,'/ROW/DNAME') DEPARTMENT_NAME  
3. FROM (  
4. SELECT XMLType(dbms_xmlgen.getXML('SELECT * FROM dept')) AS xml_data FROM dual ) a   
5. , TABLE(XMLSequence(EXTRACT(a.xml_data,'/ROWSET/ROW'))) b;   

Output

1. NO    DEPARTMENT_NO            DEPARTMENT_NAME  
2. ------------------------------------------------------------  
3. 1      10                       ACCOUNTING  
4. 2      20                       RESEARCH  
5. 3      30                       SALES  
6. 4      40                       OPERATIONS  

Example 4: 

1. WITH xml_data AS (  
2. SELECT XMLType(dbms_xmlgen.getXML('SELECT * FROM dept')) AS xml FROM dual  
3. ) ,  parsed_xml_data AS (  
4.   SELECT ROWNUM AS ID  
5.       --,   x_seq.*  
6.       ,   extractvalue(x_seq.column_value,'/ROW/DEPTNO') AS DEPARTMENT_NO  
7.       ,   extractvalue(x_seq.column_value,'/ROW/DNAME') AS DEPARTMENT_NAME  
8.       FROM xml_data x,TABLE(XMLSequence(extract(x.xml,'/ROWSET/ROW')))  x_seq  
9. )  
10. SELECT * FROM parsed_xml_data;  

Output

1. NO    DEPARTMENT_NO            DEPARTMENT_NAME  
2. ------------------------------------------------------------  
3. 1      10                       ACCOUNTING  
4. 2      20                       RESEARCH  
5. 3      30                       SALES  
6. 4      40                       OPERATIONS  

Query to Display the Table Name and the Number of Records in PostgreSQL

The following Query will give the list of table name and the corresponding Record count in PostgresPlus Advanced Server or EnterpriseDB.

1. SELECT table_name, CAST((xpath('//row/count/text()', xml_data))[1] AS varchar(50))::numeric As total_count FROM (  
2. SELECT table_name, query_to_xml( 'SELECT COUNT(1) FROM '||TABLE_NAME,true,false,'') AS xml_data FROM (SELECT * FROM USER_TABLES WHERE SCHEMA_NAME ='PUBLIC' ) AS tab  
3. ) AS xml;  

With the slight modification in the query, you can use the same in both Vanilla Postgres and PostgresPlus Advanced Server to fetch the table name and the record count. 

1. SELECT table_name, CAST((xpath('//row/count/text()', xml_data))[1] AS varchar(50))::numeric As total_count FROM (  
2. SELECT table_name, query_to_xml( 'SELECT COUNT(1) FROM '||TABLE_NAME,true,false,'') AS xml_data FROM (SELECT tablename AS table_name FROM pg_tables WHERE schemaname ='public' ) AS tab  
3. ) AS xml;