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