XML data formation using object types

Question

I need to generate XML using Oracle object types and element will have attributes. The complication is both element and attribute will have values as below

employee> ename id=100>Aaron /ename> /employee>

Requirement is to achieve this with object types only ,not to use xmlelement,xmlattributes since real scenario contains numerous number of columns.

Create or replace type ename_type as object("@id" number,ename varchar2(100));

Please help to write SQL query

Select xmlement("employee",xmlforest()) from employees;

hi man, check this answer bellow. – dani herrera Feb 07 '18 at 15:59 — dani herrera, Feb 07 '18 at 15:59

score 0 · Answer 1 · answered Dec 18 '17 at 14:21

Ok, here it is:

DDL:

create type ename_type as object  (
  id number,
  ename varchar2(100)
);

create table employees of ename_type;

insert into employees values ( ename_type( 1,'dani') );

DML to generate your XML:

Select XMLELEMENT("employee",
                  XMLATTRIBUTES(e.id AS "ID"),
                  xmlforest(e.name)) 
from employees e;

Explanation:

on DDL we create the type and a table of objects. Then, on DML, we apply XMLELEMENT function to both, attributes and forest.

More info:

You can learn about it on Database SQL Reference docs

Enjoy!

XML data formation using object types

1 Answers1