How to output XML from a regular SQL query?

Question

Oracle version 11g. I have a working SQL query in a PL/SQL procedure. What is the easiest way to take this query and output XML, either specifying the element names, or taking them from the query columns? I'm confused about whether to use DBMS_XMLGEN, XMLQuery or SQLX. What is the canonical way of doing this in Oracle?

Thanks

dbms_xmlgen should work. Have you tried it?

OldProgrammer
– OldProgrammer

2014-04-18 18:21:53 +00:00
Commented Apr 18, 2014 at 18:21 — OldProgrammer
– OldProgrammer, Commented Apr 18, 2014 at 18:21
check this link stackoverflow.com/questions/14466485/…

Eng. Samer T
– Eng. Samer T

2014-04-18 21:03:54 +00:00
Commented Apr 18, 2014 at 21:03 — Eng. Samer T
– Eng. Samer T, Commented Apr 18, 2014 at 21:03

Nabuchodonozor · Accepted Answer · 2014-05-10 19:17:51Z

4

Check the SQL Functions generating XML http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#i1029583. For example:

SELECT
   XMLRoot(
      XMLElement(
        "employees", 
        XMLAgg(
           XMLElement("employee",
              XMLConcat(
                 XMLElement(
                    "name",
                    e.name
                 ),
                 XMLElement(
                    "surname",
                    e.surname
                 )
              )
           )
        )
     ), VERSION '1.0', STANDALONE YES
  ).getClobVal() INTO retval -- useful if you want to export it outside oracle
  FROM employees e

should return for example:

<?xml version="1.0" standalone="yes"?>
<employees>
    <employee>
        <name>Jan</name>
        <surname>Kowalski</surname>
    </employee>
    <employee>
        <name>Tomasz</name>
        <surname>Nowak</surname>
    </employee>
</employees>

edited May 10, 2014 at 19:17

answered May 10, 2014 at 19:12

Nabuchodonozor

7361 gold badge6 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2740842 Over a year ago

Thanks! Better late than never.

sam Over a year ago

useful answer, but kindly can I set alias for the result column instead of that strange long name ??? because when i set an alias for it I got result in (HUGECLOB) but I need string result to be shown

Collectives™ on Stack Overflow

How to output XML from a regular SQL query?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related