Definition:
In Oracle PL/SQL, SYS_XMLGEN is a built in XML function which returns XML type instance for a row and column in the database in the form of XML document. It accepts two parameters. The first parameter can be a column value, an object type, or an XML type instance. The second parameter is an optional parameter of XMLFormat type for formatting purpose.
For a scalar column value, SYS_XMLGEN generates XML node element containing all the scalar values in the column. For UDT, it returns an XML node element containing values of all specific and inherited attributes. For an XML type instance, it just encloses the XML document in an additional 'ROW' tag.
Example Syntax:
SYS_XMLGEN([Column | UDT | XMLType], format)
Example Usage:
The SQL example below uses SYS_XMLGEN to accept a column input which contains a scalar value. Note the output is an XMLType instance containing all scalar values of the column in the table.
SQL> SELECT SYS_XMLGEN(ENAME) FROM EMPLOYEE;
SYS_XMLGEN(ENAME)
-------------------------------------------------
<ENAME>JOHN</ENAME>
<ENAME>KATE</ENAME>
<ENAME>MILLER</ENAME>
<ENAME>JOHNY</ENAME>
<ENAME>LARRY</ENAME>
<ENAME>EDWIN</ENAME>
<ENAME>KING</ENAME>
<ENAME>SUEZ</ENAME>
<ENAME>HICK</ENAME>
9 rows selected.
The SQL below uses SYS_XMLGEN to accept input of object Type. It generates XML node element containing values for all the attributes.
SQL> SELECT SYS_XMLGEN(VALUE(T))
FROM XML_SPECS T;
<?xml version="1.0"?>
<ROW>
<NAME>SPEC_TEST</NAME>
<ID>1</ID>
<SPEC_FILES>
<CCP_FILES_OT>
<NAME>CRT_CCP_CRTOBJ_TAB.SQL</NAME>
<ID>101</ID>
<FILE_PATH>C:\CCP_Script_18112010\MISC</FILE_PATH>
</CCP_FILES_OT>
<CCP_FILES_OT>
<NAME>CRT_CCP_TABOBJ_REL.SQL</NAME>
<ID>102</ID>
<FILE_PATH>C:\CCP_Script_18112010\MISC</FILE_PATH>
</CCP_FILES_OT>
</SPEC_FILES>
</ROW>
Related Links:
