Indented XML

X

XML is not cool anymore, but if you are still using it and want to have it in readable form (indented), there is a "AS CLOB INDENT SIZE" trick. There is a little side effect and that is that the empty tags (departure_at) will be shortened.

SELECT
'<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope"  xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:app="">
  <soap:Header>
    <a:Action soap:mustUnderstand="1"></a:Action>
  </soap:Header>
  <soap:Body>
    <app:message><![CDATA['|| 
    XMLSERIALIZE(DOCUMENT XMLELEMENT("messages",
        XMLAGG(XMLELEMENT("message",
            XMLATTRIBUTES(r.message_id AS "id"),
            XMLELEMENT("location",
                XMLELEMENT("country_id",    r.country_id),
                XMLELEMENT("source_id",     r.source_id)
            ),
            XMLELEMENT("departure_at",      NULL)
        ))) AS CLOB INDENT SIZE = 2                         -- this is the trick
    ) ||
    ']]></app:message>
   </soap:Body>
</soap:Envelope>' AS xml
FROM (
    SELECT
        1       AS message_id,
        'CZ'    AS country_id,
        'A'     AS source_id
    FROM DUAL
    UNION ALL
    SELECT
        1       AS message_id,
        'SK'    AS country_id,
        'B'     AS source_id
    FROM DUAL
) r;