you are a new oracle dba and you would like to know how an xml can be generated from a table data using oracle packages like dbms_xmlgen.
How to Generate XML document with Attributes using DBMS_XMLGEN Package [ID 1168854.1] | ||
|
||
Modified 23-SEP-2010 Type SAMPLE CODE Status PUBLISHED |
In this Document
Purpose
Software Requirements/Prerequisites
Configuring the Sample Code
Running the Sample Code
Caution
Sample Code
Sample Code Output
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 11.2.0.1.0 – Release: 10.2 to 11.2
Information in this document applies to any platform.
Purpose
Provides sample code on how to create XML document with “attributes” using DBMS_XMLGEN package.
Software Requirements/Prerequisites
SQL, SQL*Plus
Configuring the Sample Code
In the database JServer / XDB needs to be installed.
Running the Sample Code
Execute the script in sqlplus
Caution
This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.
Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.
Sample Code
Use the “@column” in select to generate XML document with attributes
set serveroutput on
declare
qryCtx DBMS_XMLGEN.ctxHandle;
result CLOB;
begin
qryCtx := dbms_xmlgen.newcontext(‘select empno as “@empno”, ename as “@ename” from emp where deptno = 10’);
result := dbms_xmlgen.getxml(qryCtx);
DBMS_XMLGEN.closeContext(qryCtx);
dbms_output.put_line(result);
end;
/
Reference Oracle� XML DB Developer’s Guide at
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10492/xdb13gen.htm#i1025386
starting with:
Example 18-24 DBMS_XMLGEN: Generating Nested XML using Object Types
Sample Code Output
<?xml version=”1.0″?>
<ROWSET>
<ROW empno = “7782” ename = “CLARK”>
</ROW>
<ROW empno = “7839” ename = “KING”>
</ROW>
<ROW empno = “7934” ename =”MILLER”>
</ROW>
</ROWSET>
Related
Products
|