CODEWITHSUNDEEP
sqloracle-database
user3534638
user3534638

Reputation: 1

How to prevent XML reformatting in SQL

Updated an xml file in order to remove an unnecessary field using deletexml(xmltype(xxx)).getClobVal() but the XML returns as one long string instead of a properly formatted XML file with indents and spaces. Any idea what I'm doing wrong here? Thanks

Upvotes: 0

Views: 517

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59557

I use this procedure to make a pretty XML:

PROCEDURE MakePrettyXml(xmlString IN OUT NOCOPY CLOB) IS

    xmlDocFragment DBMS_XMLDOM.DOMDOCUMENTFRAGMENT;
    xslProc DBMS_XSLPROCESSOR.PROCESSOR;
    xsl DBMS_XSLPROCESSOR.STYLESHEET;
    xmlStringOut CLOB;

BEGIN

    DBMS_LOB.CREATETEMPORARY(xmlStringOut, TRUE);
    xslProc := DBMS_XSLPROCESSOR.NEWPROCESSOR; 
    xsl := DBMS_XSLPROCESSOR.NEWSTYLESHEET(
        '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">'||
            '<xsl:output method="xml" indent="yes"/>'||
            '<xsl:template match="@*|node( )">'||
                '<xsl:copy>'||
                    '<xsl:apply-templates select="@*|node( )"/>'||
                '</xsl:copy>'||
            '</xsl:template>'||
        '</xsl:stylesheet>', NULL);

    xmlDocFragment := DBMS_XSLPROCESSOR.PROCESSXSL(p => xslProc, ss => xsl, cl => xmlString);
    DBMS_XMLDOM.WRITETOCLOB(DBMS_XMLDOM.MAKENODE(xmlDocFragment), xmlStringOut);

    DBMS_XSLPROCESSOR.FREESTYLESHEET(xsl);
    DBMS_XSLPROCESSOR.FREEPROCESSOR(xslProc);

    xmlString := xmlStringOut; 
    DBMS_LOB.FREETEMPORARY(xmlStringOut);

END MakePrettyXml;

But note the output is a CLOB rather than a XMLTYPE, you may need some additional conversions.

Upvotes: 0

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

getClobVal, getStringVal are deprecated since oracle 11.2 .instead of these function you have to use xmlserialize.

Example:

 select xmlserialize(document xmltype('<a><b><c>xxx</c></b></a>') indent size=2) from dual;

And you will end with clob object containing pretty-print xml.

Upvotes: 1

Max Vollmer
Max Vollmer

Reputation: 8598

"properly formatted XML file with indents and spaces"

That might surprise you, but that is properly formatted (well-formed) XML. The XML standard says nothing about whitespace between structural elements, except that it is allowed. It's called "insignificant white-space" for a reason.

If you want to format your XML for human-readability, you must do that yourself. But XML isn't for humans, it's for machines, so there is no reason to have your SQL do such formatting. Use any tool you like that auto-formats XML for human readability if you want to inspect the XML as human.

Upvotes: 0

Related Questions