Remove Whitespace from XML

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

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;
/ 

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.

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>