How to generate XML-output from Oracle

Question

A colleague wrote this query for me :

set linesize 150
set pagesize 0
SET HEADING OFF
SET FEEDBACK OFF
COLUMN C0 NEWLINE

select '<?XML VERSION="1.0"?>'
FROM DUAL
/
SET EMBEDDED ON

select '<row>'                                               C0
, '<field name="orgnr">'||organisatie_nr||'</field>'         C0
, '<field name="orgnaam">'||organisatie_naam_oms||'</field>' C0
, '<field name="orgwnpl">'||woonplaats_oms||'</field>'       C0
, '</row>'                                                   C0
from organisatie_eenheid
/

which generates:

<?XML VERSION="1.0"?>                                                                                                                                 
<row>                                                                                                                                                 
<field name="orgnr">910</field>                                                                                                                            
<field name="orgnaam">Dienst Regelingen</field>                                                                                                            
<field name="orgwnpl">ASSEN</field>                                                                                                                        
</row>

In MySQL mysql -u -p -X 'select organisatie_nr as orgnr, organisatie_naam_oms as orgnaam, woonplaats_oms as orgwnpl from organisatie_eenheid;' results in something like :

<?XML VERSION="1.0"?>
<resultset>
  <row>
  <field name="orgnr">910</field>
  <field name="orgnaam">Dienst Regelingen</field>
  <field name="orgwnpl">ASSEN</field>
</row>
</resultset>

I know that Oracle can also produce XML but since I never work with Oracle, I don't know the syntax.

Which Oracle query can generate the same output as MySQL?

You can see the options and syntax in the documentation. For your desired output you may want to look at dbms_xmlgen.get_xml; see this example. — Alex Poole
– Alex Poole, Commented Nov 20, 2014 at 11:09

MihaiC · Accepted Answer · 2014-11-20 10:57:14Z

Try something like this (taken from the docs):

SELECT XMLSerialize(
         DOCUMENT
         SYS_XMLGEN(
           dept_t(department_id,
                  department_name,
                  d.location_id,
                  cast(MULTISET
                       (SELECT emp_t(e.employee_id, e.last_name,
                                     e.job_id, e.manager_id, e.hire_date,
                                     e.salary, e.commission_pct)
                          FROM hr.employees e
                          WHERE e.department_id = d.department_id)
                       AS emplist_t)))
         AS CLOB)
  AS deptxml
  FROM hr.departments d WHERE department_id = 10 OR department_id = 20;

You can read more about how to convert a query to XML from the docs:

https://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm#ADXDB5041

Collectives™ on Stack Overflow

How to generate XML-output from Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related