1.Overview
The definition of SQL/XML is driven in part by the SQLX Group. Oracle supports several SQL/XML features which are subject to change as the standard evolves.
2.Prerequisite
- Basic knowledge of Oracle SQL.
- Basic understanding of ORACLE SQL & Dynamic SQL features.
3.Technologies and Tools Used
The following technologies has been used to achieve the Flexible Report Plugin functionality.
- SQL/PLSQL
4.Use Case
By using the predefined oracle functions we can generate and extract XML file.
Procedures:
Step 1:
In Oracle, it was necessary to use several database object types to create complex XML documents using SQL.
XML_QUERY
—————————————————————————————————-
<?xml version=”1.0″?>
<ROWSET>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
<EMP_LIST>
<EMP_ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>09-JUN-1981 00:00:00</HIREDATE>
<SAL>2450</SAL>
</EMP_ROW>
<EMP_ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-NOV-1981 00:00:00</HIREDATE>
<SAL>5000</SAL>
</EMP_ROW>
<EMP_ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>23-JAN-1982 00:00:00</HIREDATE>
<SAL>1300</SAL>
</EMP_ROW>
</EMP_LIST>
</DEPT>
</ROWSET>
1 row selected.
Step 2: The SQL/XML functions present in Oracle allow nested structures to be queried in a standard way with no additional database object definitions.
XMLELEMENT
The XMLELEMENT function is the basic unit for turning column data into XML fragments. In the following example, the first parameter specifies the tag name to be used and the second specifies the column that will supply the data contained within the tag.
XMLATTRIBUTES
The XMLATRIBUTES function converts column data into attributes of the parent element. The function call should contain one or more columns in a comma separated list. The attribute names will match the column names using the default uppercase unless an alias is used.
XMLFOREST
Using XMLELEMENT to deal with lots of columns is rather clumsy. Like XMLATTRIBUTES, the XMLFOREST function allows you to process multiple columns at once.
XMLAGG
So far we have just looked at creating individual XML fragments. What happens if we start dealing with multiple rows of data?
XMLROOT
The XMLROOT function allows us to place an XML declaration tag at the start of our XML document. In newer database versions, this function is either deprecated, or removed entirely. If you need and XML declaration, you should add it manually to the document.
