Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL SYS_XMLGEN Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: SYS_XMLGEN

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:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 185 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?