Reputation: 329
Query:
I have a requirement to create an XML (in Oracle) and store in the tables (CLOB format). I am getting the XML in one complete line and not in formatted way (Pretty Print way as it is mentioned in some of the places I checked).
Code:
SELECT (
XMLElement("Organization",
XMLAttributes (ROWNUM AS "ROWNUM"),
XMLForest
(
'Test' col1 ,
'Test2' col2,
'Test3' col3
)
))
AS xmlResult
-- into v_xmlResult
FROM DUAL ;
Existing Result:
<Organization ROWNUM="1"><COL1>Test</COL1><COL2>Test2</COL2><COL3>Test3</COL3></Organization>
Expected Result:
<Organization ROWNUM="1">
<COL1>Test</COL1>
<COL2>Test2</COL2>
<COL3>Test3</COL3>
</Organization>
Constraints: Can't utilize dbms_xmlgen.getxml as it's an existing code and has loads of logic in it.
Solution Tried:
select xml serialize(v_xmlResult) from dual;
Please let me know if there's a duplicate question (answered for this scenario).
Upvotes: 2
Views: 2328
Reputation: 8123
Try adding EXTRACT('*')
at the end:
SELECT (
XMLElement("Organization",
XMLAttributes (ROWNUM AS "ROWNUM"),
XMLForest
(
'Test' col1 ,
'Test2' col2,
'Test3' col3
)
)).EXTRACT('*')
AS xmlResult
-- into v_xmlResult
FROM DUAL ;
I once suggested this to somebody and it worked for him. Unfortunately, I don't have access to Oracle 10g and can't check it out. Hopefully it'll work for you as well.
If you can't modify the code, try:
select v_xmlResult.EXTRACT('*') from dual;
Upvotes: 2