User M
User M

Reputation: 329

Indent/ Format/ Pretty Type XML in Oracle 10G

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

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

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

Related Questions