10 Replies Latest reply on Feb 28, 2019 2:44 PM by Jason_(A_Non)

    Remove Whitespace from XML

    user5722493

      Hi All,

       

      I am generating an XML file using DBMS_XMLDOM.DOMNode and generating node using DBMS_XMLDOM.appendChild.

      Finally writing to file using DBMS_XMLDOM.WRITETOFILE.

       

      XML data is populating from an Oracle table.

       

      But the issue is, the XML is generating with white spaces, like below (xml is opened in Notepad++)

        

      <?xml version="1.0" encoding="UTF-8"?>

      <CampaignSchedule xmlns="http://dummy.new.com/schemas/schedule/111111">    

        <Agency>

              <AgencyName>Dummy</AgencyName>

            </Advertiser>

            <Product>

              <ProductName>Apple</ProductName>

            </Product>

       

      Where as I would like create the XML without the whitespace and if I open the same in Notepad++, should display like below:

      <?xml version="1.0" encoding="UTF-8"?><CampaignSchedule xmlns="http://dummy.new.com/schemas/schedule/111111"> <Agency> <AgencyName>Dummy</AgencyName> </Advertiser>      <Product> <ProductName>Apple</ProductName> </Product>

       

       

      It would be nice, if anyone can put some light on this issue.

       

      Thanks with Regards,

      JD

        • 1. Re: Remove Whitespace from XML
          Jason_(A_Non)

          Dup of

          Remove Whitespace from XML

          where some questions and answers already provided.

          • 2. Re: Remove Whitespace from XML
            _jum

            Compare the parameter INDENT of XMLSERIALIZE:

             

            SELECT XMLSERIALIZE(Document XMLTYPE ('<xmldata><rows><row>test</row></rows></xmldata>') AS VARCHAR2(4000) 
                NO INDENT) AS resdat
              FROM DUAL;
            
            RESDAT
            -------
            <xmldata><rows><row>test</row></rows></xmldata>
            
            
            SELECT XMLSERIALIZE(Document XMLTYPE ('<xmldata><rows><row>test</row></rows></xmldata>') AS VARCHAR2(4000) 
                INDENT) AS resdat
              FROM DUAL; 
            
            RESDAT
            -------
            <xmldata>
              <rows>
                <row>test</row>
              </rows>
            </xmldata>
            
            
            
            • 3. Re: Remove Whitespace from XML
              user5722493

              Hi,

               

              Thanks for your reply.

              Below is my code :

              declare
              l_v_xml_1 xmltype;
              l_domdoc                      DBMS_XMLDOM.DOMDocument;
              l_v_file_name varchar2(100);
              begin   
              SELECT
                    XMLELEMENT("Schedule", XMLATTRIBUTES(('fffff') AS "xmlns"),
                    XMLELEMENT("Header",
                        XMLFOREST('00004' AS "SalesIdentifier",
                           '001' AS "AgencyIdentifier"),
                        XMLELEMENT("BuyingAgency",
                           XMLFOREST('AAA' AS "AgencyName")
                           )
                       ),
                    XMLELEMENT("Months",
                       XMLELEMENT("Month",
                          XMLFOREST('3455' AS "ID",
                             '56' AS "Code"),
                          XMLELEMENT("Advertiser",
                             XMLFOREST('FFFF' AS "Name")
                              ),
                          XMLELEMENT("Product",
                             XMLFOREST('HHH' AS "Name")
                              )
                           )
                       )
                      )  
                    into l_v_xml_1
                   from dual ;
                  
              SELECT XMLSERIALIZE(Document XMLTYPE (l_v_xml_1) AS AS CLOB  
                  NO INDENT) into  l_domdoc
                  FROM DUAL;

              end;
              /

               

              But executing showing error :

              ERROR at line 31:

              ORA-06550: line 31, column 54:

              PL/SQL: ORA-00902: invalid datatype

              ORA-06550: line 31, column 2:

              PL/SQL: SQL Statement ignored

              • 4. Re: Remove Whitespace from XML
                user5722493

                I have rectified the issue. Thanks..

                 

                declare
                l_v_xml_1 CLOB; --XMLTYPE;
                l_domdoc                      DBMS_XMLDOM.DOMDocument;
                l_v_file_name varchar2(100);
                begin   
                SELECT  XMLSERIALIZE(Document
                  --SELECT
                      XMLELEMENT("Schedule", XMLATTRIBUTES(('fffff') AS "xmlns"),
                      XMLELEMENT("Header",
                          XMLFOREST('00004' AS "SalesIdentifier",
                             '001' AS "AgencyIdentifier"),
                          XMLELEMENT("BuyingAgency",
                             XMLFOREST('AAA' AS "AgencyName")
                             )
                         ),
                      XMLELEMENT("Months",
                         XMLELEMENT("Month",
                            XMLFOREST('3455' AS "ID",
                               '56' AS "Code"),
                            XMLELEMENT("Advertiser",
                               XMLFOREST('FFFF' AS "Name")
                                ),
                            XMLELEMENT("Product",
                               XMLFOREST('HHH' AS "Name")
                                )
                             )
                         )
                        )
                        NO INDENT) xml
                      into l_v_xml_1
                     from dual ;
                    

                end;

                • 5. Re: Remove Whitespace from XML
                  _jum

                  Yepp, there was a typo (AS AS) and XMLSERIALIZE gives a STRING or a LOB.

                  • 6. Re: Remove Whitespace from XML
                    user5722493

                    sorry did not work my code as per my expectation :

                    declare
                    l_v_xml_1 CLOB;
                    l_domdoc                            DBMS_XMLDOM.DOMDocument;
                    l_v_file_name varchar2(100);
                    begin                                                    
                    SELECT  XMLSERIALIZE(Document
                        XMLELEMENT("Schedule", XMLATTRIBUTES(('http://caria.optimad.com/spots/schemas/schedule/20121001') AS "xmlns"),
                         XMLELEMENT("Header",
                         XMLFOREST('00004' AS "SalesIdentifier",
                            '001' AS "AgencyIdentifier"),
                             XMLELEMENT("BuyingAgency",
                              XMLFOREST('AAA' AS "AgencyName")
                            )
                            ),
                         XMLELEMENT("Months",
                          XMLELEMENT("Month",
                           XMLFOREST('3455' AS "ID",
                            '56' AS "Code"),
                           XMLELEMENT("Advertiser",
                            XMLFOREST('FFFF' AS "Name")
                              ),
                             XMLELEMENT("Product",
                              XMLFOREST('HHH' AS "Name")
                                )
                             )
                            )
                           )
                        NO INDENT)    as   xml        
                    into l_v_xml_1
                    from dual            ;
                      l_domdoc := DBMS_XMLDOM.NEWDOMDOCUMENT(l_v_xml_1);
                      l_v_file_path_name := 'C:\Test\Sample_file';

                    -- DBMS_XMLDOM.WRITETOFILE(l_domdoc, l_v_file_name,l_v_nls_encoding_char);
                       DBMS_XMLDOM.WRITETOFILE(l_domdoc, l_v_file_path_name);
                    end;

                     

                    the file is generating removing the whitespace, but unfortunately not generating XML type file.

                     

                    Any one could please help in this regards.

                    • 7. Re: Remove Whitespace from XML
                      Jason_(A_Non)
                      but unfortunately not generating XML type file.

                      Do you mean the file name on the OS does not end in .xml?

                      How about you add it to the name you set.

                      l_v_file_path_name := 'C:\Test\Sample_file';

                      • 8. Re: Remove Whitespace from XML
                        user5722493

                        Hi Jason,

                        you are correct. the file is generated on the OS is not as  'XML Document', rather showing type as 'File'.

                         

                        For the file_path_name I am just showing as an example that the file will be generated on an OS path, for that the related code also I have written, which unnecessary I have not mentioned here. 

                         

                        My requirement is, I would like generate an XML Document using PL/SQL code where file will have no Whitespace.

                         

                        I am able to generate the file like below:

                         

                        <?xml version="1.0" encoding="UTF-8"?>

                        <CampaignSchedule xmlns="http://dummy.new.com/schemas/schedule/111111">    

                          <Agency>

                                <AgencyName>Dummy</AgencyName>

                              </Advertiser>

                              <Product>

                                <ProductName>Apple</ProductName>

                              </Product>

                         

                        Where as I would like create the XML as below format   :

                         

                        <?xml version="1.0" encoding="UTF-8"?><CampaignSchedule xmlns="http://dummy.new.com/schemas/schedule/111111"> <Agency> <AgencyName>Dummy</AgencyName> </Advertiser>      <Product> <ProductName>Apple</ProductName> </Product>

                        • 9. Re: Remove Whitespace from XML
                          _jum

                          Your anwsers are contradictory:

                          user5722493 schrieb:

                           

                          #6: > the file is generating removing the whitespace, but unfortunately not generating XML type file.

                          #7:> My requirement is, I would like generate an XML Document using PL/SQL code where file will have no Whitespace.

                          In my reply I gave you the example code to create XML with INDENT or without ( NO INDENT) and Jason_(A_Non)

                          • 10. Re: Remove Whitespace from XML
                            Jason_(A_Non)

                            Yes, you seem to not understand how filenames work.  I'm guessing you are using Windows, which tends to hide the type/extension when showing a file name.

                            Feel free to understand the basics

                            https://en.wikipedia.org/wiki/Filename

                             

                            The sample line I mentioned is missing the type/extension for the full filename.  You have the path, you have the file, just add the type/extension.

                             

                            No I'm not going to show the corrected line of your code as it should be as you should be aware of how to properly name a file.

                             

                            Back to your white space issue in the file on disk, you need to have a better understanding of what your code is doing.

                            1) The XMLSerialize command returns a CLOB (by default) that is a single string of text with no extra spaces that you put into a CLOB variable

                            2) You use that CLOB variable as input to create a DOMDocument.  This parses the string and creates an XML representation of the string

                            3) You write the reformatted DOMDocument to disk.  The internal engine that Oracle uses parses the DOMDocument and writes it out in a human readable pretty print format (aka spaces/indents)

                             

                            The solution.

                            You don't need steps 2 and 3.  Just use dbms_xslprocessor.clob2file to write the CLOB directly to disk as is.  It is already in the format you need.  Please look up examples on how to use clob2file as it requires a DIRECTORY object name instead of putting 'C:\Test'