CODEWITHSUNDEEP
xmloracle-databasestored-proceduresplsqlxmltype
aengas
aengas

Reputation: 155

Pretty formatting of an XML stored in a clob in Oracle

In my Oracle database I have a table called "TestTable", with a primary key column and a column called "Message" which has the type CLOB.

Into my procedure I get an XML like this <?xml version="1.0" encoding="utf-8" ?><TestMessage><Description>This is a test message</Description></TestMessage>

My procedure looks something like this:

procedure StoreMessage(p_MessageId in raw, p_Message in clob) begin
update TestTable set Message = p_Message where MessageId = p_MessageId; end

The procedure should store the incoming XML in the clob column with proper formatting like this: <?xml version="1.0" encoding="utf-8" ?> <TestMessage> <Description>This is a test message</Description> </TestMessage>

How can I store the message as a clob, formatted properly as an XML and preserving the same encoding?

The NLS_CHARACTERSET in my database is WE8MSWIN1252 so i can't use XMLSERIALIZE which will change the encoding to WINDOWS-1252.

Upvotes: 0

Views: 1365

Answers (1)

TimDC
TimDC

Reputation: 121

I would hesitate to store formatting of the XML along with the data.

Is there a specific reason it must be stored as a CLOB? If you store the XML as an Oracle XMLTYPE, then validation and other methods are at your fingertips. Internally I believe it still stores as CLOB.

For formatting and presentation, I would keep that out of the database, and use something (in my case) like vkbeautify (and jQuery) (the XML is presented in a DIV with class: 'xmlcode':

<script type="text/javascript" src="~/Scripts/vkbeautify.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $(".xmlcode").each(function( index ) {
            var decoded = unescapeHtml($(this).html());
            var clean = vkbeautify.xml(decoded).trim();
            $(this).html(escapeHtml(clean));
        });
        $(".xmlcode").wrapInner("<pre></pre>");

    });

    function unescapeHtml(safe) {
        return $('<div />').html(safe).text();
    }
    function escapeHtml(unsafe) {
        return $('<div />').text(unsafe).html()
    }

</script>

This will create line breaks and indents that I think are what you're looking for, assumming you are displaying the data on a webpage

Upvotes: 1

Related Questions