8 Replies Latest reply: Oct 5, 2012 3:33 AM by odie_63 RSS

    generate XML

    INRi
      i want to generate xml of my database table with data??


      SELECT XMLELEMENT("Employees",
      XMLAGG(
      XMLELEMENT("Emp",
      XMLFOREST(
      e.empno AS "empno",
      e.ename AS "ename",
      e.job AS "job",
      e.mgr AS "mgr",
      e.hiredate AS "hiredate",
      e.sal AS "sal",
      e.comm AS "comm")
      )
      )
      ) AS Employee
      FROM emp e

      how to write output of this to a txt/xml file?

      Edited by: insa on Oct 4, 2012 4:10 AM
        • 1. Re: generate XML
          AlexAnd
          look at dbms_xslprocessor.clob2file

          example - Re: XML 1header and n detail records
          • 2. Re: generate XML
            INRi
            when i am query for more than 75 row the generated xml not able to open in xml editor,but it is fine for 74 rows.
            is there any size lmitataion for xml??
            Here my table has 28733 record.i want to generate xml for these many of records.
            • 3. Re: generate XML
              AlexAnd
              >
              is there any size lmitataion for xml??
              >
              Bug 12418354 - DBMS_XSLPROCESSOR.clob2file fails with ORA-1426 for large(>2g) CLOBs [ID 12418354.8]

              but i think you have <2g

              also post your code

              also you can try DBMS_XMLDOM.WRITETOFILE
              xdata  XMLTYPE;    
              ...
              doc := DBMS_XMLDOM.NewDOMDocument(xdata);                                                                            
               DBMS_XMLDOM.WRITETOFILE(doc, 'UTLDATA/marco.xml');  
              from http://www.liberidu.com/blog/2008/02/14/howto-saving-xml-data-directly-to-disk/

              or dbms_lob like
              dbms_lob.open(xmlString,DBMS_LOB.LOB_READONLY);
              loop
              -- read the lob data
              dbms_lob.read(xmlString,amount,position,charString);
              utl_file.put_line(fileHandle, charString);
              position := position + amount;
              end loop;
              • 4. Re: generate XML
                odie_63
                Before rushing to less efficient alternatives, let's try to understand your issue.
                when i am query for more than 75 row the generated xml not able to open in xml editor,but it is fine for 74 rows.
                What does "not able to open" mean?

                - Which XML editor is that?
                - Do you get any error?

                Open the file with a text editor first and see if it looks OK.
                • 5. Re: generate XML
                  INRi
                  When i m trying to open it on XML editor,following error showing-

                  Unable to load: XML parse error.
                  Error on line 3 at position 638.

                  An invalid character was found in text content.
                  • 6. Re: generate XML
                    AlexAnd
                    what about text editor? for example notepad++ ;)

                    do you have any & or other like ?
                    try escape it by utl_i18n.escape_reference as in odie_63' example - Re: The XML page cannot be displayed Cannot view XML input using XSL stylesheet
                    • 7. Re: generate XML
                      odie_63
                      insa wrote:
                      When i m trying to open it on XML editor,following error showing-

                      Unable to load: XML parse error.
                      Error on line 3 at position 638.

                      An invalid character was found in text content.
                      Again, what XML editor are you using? Web browser (IE, Firefox)?

                      Can you paste the file here?
                      • 8. Re: generate XML
                        odie_63
                        AlexAnd wrote:
                        do you have any & or other like ?
                        try escape it by utl_i18n.escape_reference
                        If SQL/XML functions are effectively used to build the XML document, there's no need to do that, reserved characters are automatically escaped.