generate xml from table using dbms_xmlgen – for the new oracle dba





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


  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Enterprise Edition
Author: admin