1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

CLOB removing newline characters

Discussion in 'SQL PL/SQL' started by tarandeep.singh, Dec 13, 2008.

  1. HI,

    I am persisting xml with below mention tag which contains preformatted text data.
    Code (Text):

    <SECTION>
    <HDG><![CDATABusiness Summary]></HDG>
    <BODY>
    <![CDATA[(C) 2008 D&B
    COPYRIGHT 2008 DUN & BRADSTREET INC. - PROVIDED UNDER CONTRACT
    FOR THE EXCLUSIVE USE OF SUBSCRIBER 263763803.
    ATTN: null

    US TEST COMPANY 993 DUNS: 36-252-4527 RATING DS
    US FICTITIOUS COMPANY 993
    BUSINESS SERVICES EMPLOYS UNDETERMINED
    899 EATON AVE SIC NO.
    BETHLEHEM PA 18025 7389
    TEL: 610 882-0005
    WILLIAM DOE, MANAGER

    RECORD TYPE: DUNS SUPPORT
    THE "DS" INDICATOR ASSIGNED TO THIS BUSINESS MEANS THAT THE LIMITED INFORMATION
    CURRENTLY IN THE D&B FILE DOES NOT ALLOW US TO CLASSIFY IT WITHIN OUR RATING
    SYSTEM.

    WE ARE PROVIDING THIS INFORMATION TO YOU IN THE INTEREST OF SPEED WITHOUT
    HAVING COMPLETED AN INVESTIGATION. THEREFORE, THIS REPORT MAY NOT REFLECT THE
    CURRENT STATUS OF THIS BUSINESS. D&B CAN INVESTIGATE THIS BUSINESS AND UPDATE
    THIS INFORMATION BASED ON THE RESULTS OF THAT INVESTIGATION.

    ]]></BODY>
    </SECTION>
     
    Now, when i retrieve this xml from oracle CLOB column, i am getting data in body tag as below:

    Code (Text):

    (C) 2008 D&B COPYRIGHT 2008 DUN & BRADSTREET INC. - PROVIDED UNDER CONTRACT FOR THE EXCLUSIVE USE OF SUBSCRIBER
     263763803. ATTN: null US TEST COMPANY 984 DUNS: 36-252-8379 RATING DS US FICTITIOUS COMPANY 984 BUSINESS SERVICES
    EMPLOYS UNDETERMINED 899 EATON AVE SIC NO. BETHLEHEM PA 18025 7389 TEL: 610 882-0005 RICHARD DOE, MANAGER RECORD
    TYPE: DUNS SUPPORT THE "DS" INDICATOR ASSIGNED TO THIS BUSINESS MEANS THAT THE LIMITED INFORMATION CURRENTLY IN
    THE D&B FILE DOES NOT ALLOW US TO CLASSIFY IT WITHIN OUR RATING SYSTEM. WE ARE PROVIDING THIS INFORMATION TO YOU
    IN THE INTEREST OF SPEED WITHOUT HAVING COMPLETED AN INVESTIGATION. THEREFORE, THIS REPORT MAY NOT REFLECT THE
    CURRENT STATUS OF THIS BUSINESS. D&B CAN INVESTIGATE THIS BUSINESS AND UPDATE THIS INFORMATION BASED ON THE
    RESULTS OF THAT INVESTIGATION.
     
    All text formatting goes for a toss (newline, trailing tab characters are getting trimmed).

    Could you please provide any inputs on how to avoid this. Text formatting needs to be preserved.

    thanks,
    taran
     
  2. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    hi taran

    if you are using CLOB storage for XMLType data, the XML document is stored in an XMLType column or table as a complete text document so while retrieving you will get the output as u have shown.

    Updating an XML document stored as an XMLType CLOB will be a very tedious operation and it will have to involve DOM parsing the document, then performing the update operation on the DOM representation of the document, serializing the updated document back into text, and finally replacing it.

    I suggest you store XML as structured data and register an XML Schema and store XML in an XML-schema based XMLType. Using Native XMLType structured storage, your XML document would conform to the XML schema and will be stored in the database as a set of SQL objects. This will provide better DML options.