Wijaya's Blog: Oracle XMLDB: Create XML data with ease

It is a fact that we, as software developer, will encounter more and more data in XML format. Either we receive xml data or actually need to produce one for information sharing with other system (web service, legacy system, .net vs Java, etc...). XMLDB of Oracle database (version 9i Release 2 and above) provides us with a convenience data type (XMLType) to store xml data, as well as the full set of functions for manipulating and accessing it. Action speak loader than words. So let's see how easy XMLDB actually is... Using the default SCOTT schema, let's assume that we need to create xml data showing all employee details. We shall create 2 tables: 1. dept_xml: a huge xml doc of all department and the employee within it. 2. emp_xml: all employee information in a xml snippet And here we go: 1. Create the tables with XMLType column for storing the xml data.

scott> create table dept_xml(
  2    xml     XMLType);

Table created.

scott> create table emp_xml(
  2    xml     XMLType);

Table created.

2. Transform the relational data from DEPT and EMP table into xml document using SQL functions and PL/SQL

 
-- insert a big xml doc to dept_xml  table

scott> insert into dept_xml values ((SELECT XMLRoot(XMLElement("ORG_CHART", XMLAgg(XMLElement("DEPARTMENT",
  2         XMLAttributes(d.deptno AS "DEPTNO",
  3                       d.dname AS "NAME",
  4                       d.loc AS "LOCATION"),
  5          XMLForest(
  6           (SELECT XMLAgg(
  7                     XMLElement(
  8                       "EMPLOYEE",
  9                       xmlforest(e.empno, 
 10                                 e.ename,
 11                                 e.job,
 12                                 e.mgr,
 13                                 e.hiredate,
 14                                 e.sal,
 15                                 e.comm,
 16                                 s.grade
 17                       )
 18                     )
 19                   )
 20             FROM emp e, salgrade s
 21             WHERE e.deptno = d.deptno
 22             AND e.sal BETWEEN s.losal and s.hisal
 23           ) "EMPLOYEES"
 24         )
 25       )))) "XML"
 26  FROM dept d
 27  ));
 
1 row created.

-- insert each employee data as a separate xml doc

scott> DECLARE
  2    empNo  NUMBER;
  3    mysql  VARCHAR2(512); 
  4    CURSOR empNoCursor IS
  5      select empno
  6      from emp;
  7  BEGIN
  8    OPEN empNoCursor;
  9    LOOP
 10      FETCH empNoCursor INTO empNo;
 11      EXIT WHEN empNoCursor %NOTFOUND;
 12      
 13      --DBMS_OUTPUT.put_line('empNo: ' || empNo);
 14  
 15      mysql := 'insert into emp_xml values ((SELECT XMLRoot( ' ||
 16               '  XMLElement( ' ||
 17               '  "EMPLOYEE", ' ||
 18               '  xmlforest(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno, s.grade))) ' ||
 19               ' )) ' ||
 20               ' FROM emp e, salgrade s ' ||
 21               ' WHERE e.empno = :1 ' ||                    
 22               ' AND e.sal BETWEEN s.losal and s.hisal ' || 
 23               ' )) ';                                      
 24                                                            
 25      EXECUTE IMMEDIATE mysql using empNo;                  
 26                                                            
 27    END LOOP;                                               
 28    CLOSE empNoCursor;                                      
 29    COMMIT;                                                 
 30  END;                                                      
 31  /       

PL/SQL procedure successfully completed.

Here we use the following XML SQL Utility: XMLRoot: create a XML document of XMLType XMLAgg: aggregate a bunch of XML elements and group it as one forest XMLElement: create a XML element XMLAttributes: create a XML attribute XMLForest: take XMLAgg result and group them as a XML node Please note that for dept_xml entry, we also created our custom tag named: ORG_CHART, DEPARTMENT, EMPLOYEES, and EMPLOYEE Check Oracle documentation for syntax and more explanation. 3. Final result:

XML
-------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<ORG_CHART>
  <DEPARTMENT DEPTNO="10" NAME="ACCOUNTING" LOCATION="NEW YORK">
    <EMPLOYEES>
      <EMPLOYEE>
        <EMPNO>7839</EMPNO>
        <ENAME>KING</ENAME>
        <JOB>PRESIDENT</JOB>
        <HIREDATE>1981-11-17</HIREDATE>
        <SAL>5000</SAL>
        <GRADE>5</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7782</EMPNO>
        <ENAME>CLARK</ENAME>
        <JOB>MANAGER</JOB>
        <MGR>7839</MGR>
        <HIREDATE>1981-06-09</HIREDATE>
        <SAL>2450</SAL>
        <GRADE>4</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7934</EMPNO>
        <ENAME>MILLER</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7782</MGR>
        <HIREDATE>1982-01-23</HIREDATE>
        <SAL>1300</SAL>
        <GRADE>2</GRADE>
      </EMPLOYEE>
    </EMPLOYEES>
  </DEPARTMENT>
  <DEPARTMENT DEPTNO="20" NAME="RESEARCH" LOCATION="DALLAS">
    <EMPLOYEES>
      <EMPLOYEE>
        <EMPNO>7788</EMPNO>
        <ENAME>SCOTT</ENAME>
        <JOB>ANALYST</JOB>
        <MGR>7566</MGR>
        <HIREDATE>1987-04-19</HIREDATE>
        <SAL>3000</SAL>
        <GRADE>4</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7902</EMPNO>
        <ENAME>FORD</ENAME>
        <JOB>ANALYST</JOB>
        <MGR>7566</MGR>
        <HIREDATE>1981-12-03</HIREDATE>
        <SAL>3000</SAL>
        <GRADE>4</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7566</EMPNO>
        <ENAME>JONES</ENAME>
        <JOB>MANAGER</JOB>
        <MGR>7839</MGR>
        <HIREDATE>1981-04-02</HIREDATE>
        <SAL>2975</SAL>
        <GRADE>4</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7876</EMPNO>
        <ENAME>ADAMS</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7788</MGR>
        <HIREDATE>1987-05-23</HIREDATE>
        <SAL>1100</SAL>
        <GRADE>1</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7369</EMPNO>
        <ENAME>SMITH</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7902</MGR>
        <HIREDATE>1980-12-17</HIREDATE>
        <SAL>800</SAL>
        <GRADE>1</GRADE>
      </EMPLOYEE>
    </EMPLOYEES>
  </DEPARTMENT>
  <DEPARTMENT DEPTNO="30" NAME="SALES" LOCATION="CHICAGO">
    <EMPLOYEES>
      <EMPLOYEE>
        <EMPNO>7698</EMPNO>
        <ENAME>BLAKE</ENAME>
        <JOB>MANAGER</JOB>
        <MGR>7839</MGR>
        <HIREDATE>1981-05-01</HIREDATE>
        <SAL>2850</SAL>
        <GRADE>4</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7499</EMPNO>
        <ENAME>ALLEN</ENAME>
        <JOB>SALESMAN</JOB>
        <MGR>7698</MGR>
        <HIREDATE>1981-02-20</HIREDATE>
        <SAL>1600</SAL>
        <COMM>300</COMM>
        <GRADE>3</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7844</EMPNO>
        <ENAME>TURNER</ENAME>
        <JOB>SALESMAN</JOB>
        <MGR>7698</MGR>
        <HIREDATE>1981-09-08</HIREDATE>
        <SAL>1500</SAL>
        <COMM>0</COMM>
        <GRADE>3</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7521</EMPNO>
        <ENAME>WARD</ENAME>
        <JOB>SALESMAN</JOB>
        <MGR>7698</MGR>
        <HIREDATE>1981-02-22</HIREDATE>
        <SAL>1250</SAL>
        <COMM>500</COMM>
        <GRADE>2</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7654</EMPNO>
        <ENAME>MARTIN</ENAME>
        <JOB>SALESMAN</JOB>
        <MGR>7698</MGR>
        <HIREDATE>1981-09-28</HIREDATE>
        <SAL>1250</SAL>
        <COMM>1400</COMM>
        <GRADE>2</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7900</EMPNO>
        <ENAME>JAMES</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7698</MGR>
        <HIREDATE>1981-12-03</HIREDATE>
        <SAL>950</SAL>
        <GRADE>1</GRADE>
      </EMPLOYEE>
    </EMPLOYEES>
  </DEPARTMENT>
  <DEPARTMENT DEPTNO="40" NAME="OPERATIONS" LOCATION="BOSTON"/>
</ORG_CHART>

scott> select * from emp_xml;

XML
-------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>1980-12-17</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
  <GRADE>1</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7499</EMPNO>
  <ENAME>ALLEN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>1981-02-20</HIREDATE>
  <SAL>1600</SAL>
  <COMM>300</COMM>
  <DEPTNO>30</DEPTNO>
  <GRADE>3</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7521</EMPNO>
  <ENAME>WARD</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>1981-02-22</HIREDATE>
  <SAL>1250</SAL>
  <COMM>500</COMM>
  <DEPTNO>30</DEPTNO>
  <GRADE>2</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7566</EMPNO>
  <ENAME>JONES</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>1981-04-02</HIREDATE>
  <SAL>2975</SAL>
  <DEPTNO>20</DEPTNO>
  <GRADE>4</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7654</EMPNO>
  <ENAME>MARTIN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>1981-09-28</HIREDATE>
  <SAL>1250</SAL>
  <COMM>1400</COMM>
  <DEPTNO>30</DEPTNO>
  <GRADE>2</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7698</EMPNO>
  <ENAME>BLAKE</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>1981-05-01</HIREDATE>
  <SAL>2850</SAL>
  <DEPTNO>30</DEPTNO>
  <GRADE>4</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7782</EMPNO>
  <ENAME>CLARK</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>1981-06-09</HIREDATE>
  <SAL>2450</SAL>
  <DEPTNO>10</DEPTNO>
  <GRADE>4</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7788</EMPNO>
  <ENAME>SCOTT</ENAME>
  <JOB>ANALYST</JOB>
  <MGR>7566</MGR>
  <HIREDATE>1987-04-19</HIREDATE>
  <SAL>3000</SAL>
  <DEPTNO>20</DEPTNO>
  <GRADE>4</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7839</EMPNO>
  <ENAME>KING</ENAME>
  <JOB>PRESIDENT</JOB>
  <HIREDATE>1981-11-17</HIREDATE>
  <SAL>5000</SAL>
  <DEPTNO>10</DEPTNO>
  <GRADE>5</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7844</EMPNO>
  <ENAME>TURNER</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>1981-09-08</HIREDATE>
  <SAL>1500</SAL>
  <COMM>0</COMM>
  <DEPTNO>30</DEPTNO>
  <GRADE>3</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7876</EMPNO>
  <ENAME>ADAMS</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7788</MGR>
  <HIREDATE>1987-05-23</HIREDATE>
  <SAL>1100</SAL>
  <DEPTNO>20</DEPTNO>
  <GRADE>1</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7900</EMPNO>
  <ENAME>JAMES</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7698</MGR>
  <HIREDATE>1981-12-03</HIREDATE>
  <SAL>950</SAL>
  <DEPTNO>30</DEPTNO>
  <GRADE>1</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7902</EMPNO>
  <ENAME>FORD</ENAME>
  <JOB>ANALYST</JOB>
  <MGR>7566</MGR>
  <HIREDATE>1981-12-03</HIREDATE>
  <SAL>3000</SAL>
  <DEPTNO>20</DEPTNO>
  <GRADE>4</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7934</EMPNO>
  <ENAME>MILLER</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7782</MGR>
  <HIREDATE>1982-01-23</HIREDATE>
  <SAL>1300</SAL>
  <DEPTNO>10</DEPTNO>
  <GRADE>2</GRADE>
</EMPLOYEE>

14 rows selected.

As you can see, creating XML data from traditional relational table is a breeze with Oracle XML utilities. Instead of having to retrieve the table data, create a XML document in the middle tier, and storing back to the database, we achieve the goal in just a one SQL statement! Not to mention the performance gain and scalability of the system. In next blog, we shall see how we can access these XML info with ease, too. Stay tune...

3 Comments:

Anonymous said...: Thanks for the example of XML DB, a great way to help me jump in and see it being used.

Minor note on the sql for the emp query because no data is retrieved into the xml result set from the dept table it does not need to be in the list of tables and does then does not need the join

So the following is extra work in the query that has no effect on the end result.

FROM ... , dept d ' ||
' WHERE e.deptno = d.deptno ' ||

Thanks Wijaya, now I have to go figure out how to do the same with my clients data.

Paul; 10/03/2006 2:06 AM
Wijaya Kusumo said...: You're right Paul. Thank you for spotting that, and good luck with your work.; 10/03/2006 10:00 AM
sap support costs said...: Oracle and XML both are my favorite. I enjoyed working on both. I try different programs for practice. I understand the code given in the post but I find it bit lengthy. I think it can be done in a shorter way. If you have any idea then share it with us.; 11/05/2011 2:36 AM

<< Home

Wijaya's Blog

Tuesday, August 02, 2005

Oracle XMLDB: Create XML data with ease

3 Comments:

About Me

Previous Posts