Format the XML String Generated using Oracle XMLAgg

Question

I am using the following query to generate an XML representation of the data. I expect the output to be idented like a regular XML format. Instead I receive a continuous string. I have tried appending CHr(13)||Chr(10) in my select statement but I don't get the correct output. Can someone provide insight on how the XML can be formatted? Thank you!

SQL Query:

SELECT
  XMLElement("Sample-Test" ,
      XMLAgg(
      XMLElement("Sample",
            XMLElement("SAMPLE_NUM", s.sample_number), 
            XMLElement("LABEL_ID", s.label_id),
            XMLElement("STATUS", s.status),
             (SELECT 
                XMLAgg( 
                XMLElement("Test-Details",
                    XMLElement("TEST_NUM", t.test_number),
                    XMLElement("ANALYSIS", t.analysis),                                                                   
                        (SELECT 
                         XMLAgg( 
                            XMLElement("Result-Details",
                            XMLElement("RESULT_NUM", R.RESULT_NUMBER),
                            XMLElement("RESULT_NAME", R.NAME))) 
                            FROM RESULT R WHERE t.test_number = R.test_number 
                            and t.SAMPLE_number = R.SAMPLE_NUMBER
                                                        )))                                                                            
               FROM TEST T WHERE t.SAMPLE_number = S.SAMPLE_NUMBER))) 
               ).getClobVal()  as XML                                             
 FROM sample s 
 WHERE s.sample_number = 720000020018; 

Current Output: (CLOB) 720000020018720000020018C21ALT_S90_NONSPE_00121Alanine Aminotransferase22GLUCOSE_S90_NONSPE_00122Glucose

Expected Output:

<Sample-Test>
    <Sample>
    <SAMPLE_NUM>720000020018</SAMPLE_NUM>
    <LABEL_ID>720000020018</LABEL_ID>
    <STATUS>C</STATUS>
        <Test-Details>
            <TEST_NUM>21</TEST_NUM>
            <ANALYSIS>ALT_S90_NONSPE_001</ANALYSIS>
                <Result-Details>
                    <RESULT_NUM>21</RESULT_NUM>
                    <RESULT_NAME>Alanine Amino</RESULT_NAME>
                </Result-Details>
        </Test-Details>
    </Sample>
</Sample-Test>

Answer 1

You have to use XMLSERIALIZE:

SELECT
  XMLSERIALIZE(DOCUMENT
    XMLElement("Sample-Test" ,
        XMLAgg(
        XMLElement("Sample",
              XMLElement("SAMPLE_NUM", s.sample_number), 
              XMLElement("LABEL_ID", s.label_id),
              XMLElement("STATUS", s.status),
               (SELECT 
                  XMLAgg( 
                  XMLElement("Test-Details",
                      XMLElement("TEST_NUM", t.test_number),
                      XMLElement("ANALYSIS", t.analysis),                                                                   
                          (SELECT 
                           XMLAgg( 
                              XMLElement("Result-Details",
                              XMLElement("RESULT_NUM", R.RESULT_NUMBER),
                              XMLElement("RESULT_NAME", R.NAME))) 
                              FROM RESULT R WHERE t.test_number = R.test_number 
                              and t.SAMPLE_number = R.SAMPLE_NUMBER
                                                          )))                                                                            
                 FROM TEST T WHERE t.SAMPLE_number = S.SAMPLE_NUMBER))) 
                 ) AS CLOB INDENT SIZE = 2) as XML                                             
 FROM sample s 
 WHERE s.sample_number = 720000020018;

Edit

It is not working for you, because, most probably, you are using Oracle 10g, and the INDENT option was introduced in version 11g. If this is the case, try below approach with the EXTRACT('*'):

SELECT
        XMLElement("Sample-Test" ,
            XMLAgg(
            XMLElement("Sample",
                  XMLElement("SAMPLE_NUM", s.sample_number), 
                  XMLElement("LABEL_ID", s.label_id),
                  XMLElement("STATUS", s.status),
                   (SELECT 
                      XMLAgg( 
                      XMLElement("Test-Details",
                          XMLElement("TEST_NUM", t.test_number),
                          XMLElement("ANALYSIS", t.analysis),                                                                   
                              (SELECT 
                               XMLAgg( 
                                  XMLElement("Result-Details",
                                  XMLElement("RESULT_NUM", R.RESULT_NUMBER),
                                  XMLElement("RESULT_NAME", R.NAME))) 
                                  FROM RESULT R WHERE t.test_number = R.test_number 
                                  and t.SAMPLE_number = R.SAMPLE_NUMBER
                                                              )))                                                                            
                     FROM TEST T WHERE t.SAMPLE_number = S.SAMPLE_NUMBER))) 
                     ).EXTRACT('*') as XML                                             
     FROM sample s 
     WHERE s.sample_number = 720000020018;