Generating Nested XML using oracle sql XMLElement function in oracle 19c

Question

I need to create a report but the output needs to be in XML format.

I'm following oracle docs, Example 18-3 XMLELEMENT: Generating Nested XML

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

SELECT XMLElement("record",
         XMLElement("PersonRegNumber", p.person_reg_number),
         XMLElement("first_name", p.first_name)
        ) AS "record"
FROM person p
where p.id in (1, 2, 3, 4, 5);

but I'm not getting the desired output, more exactly I'm getting back (XMLTYPE)

I'm not sure what I'm doing wrong, any hints will be very appreciated.

Thanks.

XMLType is correct. What client software are you using? you should be able to drill down on that and see the XML. — OldProgrammer
– OldProgrammer, Commented Dec 30, 2020 at 18:27
@OldProgrammer right thanks, just noticed that. I'm using sql develor. How do I see the data? right now is hidden, I have to right click on the row to see it. — Mariana
– Mariana, Commented Dec 30, 2020 at 18:31

OldProgrammer · Accepted Answer · 2020-12-30 18:38:38Z

1

You can also convert the XMLTYPE to a string if needed with the getClobVal() function:

SELECT XMLElement("record",
         XMLElement("PersonRegNumber", p.person_reg_number),
         XMLElement("first_name", p.first_name)
        ).getClobVal() AS "record"
FROM person p
where p.id in (1, 2, 3, 4, 5);

answered Dec 30, 2020 at 18:38

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

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

Collectives™ on Stack Overflow

Generating Nested XML using oracle sql XMLElement function in oracle 19c

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