14 Replies Latest reply: Mar 1, 2013 4:55 PM by user302631 RSS

    xml file opened in a notepad

    user302631
      Hello All,

      Is there a way open xml file in notepad and not loose the format?

      Thanks,
      S.
        • 1. Re: xml file opened in a notepad
          Marwim
          What do you mean wirh
          loose the format
          Could it be that the file has Unix line endings? Notepad cannot handle them and displays the whole content in a single line. There is no workaround except another tool that replaces Unix line endings with Windows line endings.

          Regards
          Marcus
          • 2. Re: xml file opened in a notepad
            user302631
            Hello Marcus,

            Thanks for the response Marcus.
            I am generating a xml file using xml db.
            If you open in IE browser it is displaying

            <ICR>
            <LIST_G_QH_USER />
            <LIST_G_CASECNT>
            <G_CASECNT>
            <CASECNT>91977</CASECNT>
            </G_CASECNT>
            </LIST_G_CASECNT>
            </ICR>

            But if you open in notepad it is loosing the format displaying in a single line. Please find an example below.
            Client wants the output to be displayed in the above format when the file is opened in notepad.

            <ICR><LIST_G_QH_USER /><LIST_G_CASECNT><G_CASECNT><CASECNT>91977</CASECNT></G_CASECNT></LIST_G_CASECNT></ICR>

            Thanks,
            S.
            • 3. Re: xml file opened in a notepad
              Jason_(A_Non)
              You have your concepts reversed. Notepad is showing you the file in the format that it was generated with using the database. IE is adding the format to make the XML more human readable.

              The two formats are equal in terms of processing and there should be no valid reason the client needs the XML in human readable format. That said, if they insist, then you will need to generate it from the DB in the desired format. One way is to use [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions252.htm#SQLRF06231]XMLSERIALIZE  to produce pretty-printed human readable XML.

              Notepad is not the best editor for XML. Many modern XML editors provide their own built-in ability to pretty print the XML, regardless of the format the XML from the file is in.
              • 4. Re: xml file opened in a notepad
                user302631
                Thanks for the response A non.

                I can add a linefeed in the code by using CHR(13).
                But I am using xmlforest, how can I concatenate Chr(13)?

                I can use xmlelement for each column and concatenate CHR(13) but I don't want to display empty tags.


                SELECT XMLELEMENT("EMPLOYEE",
                XMLFOREST( ename, job, mgr, hiredate, sal, comm) AS "EMP",
                xmlforest( xmlforest( dname, loc)as "DEPARTMENT" ))
                from
                emp e, dept d
                where e.deptno = d.deptno

                OUTPUT:

                <EMPLOYEE><ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><HIREDATE>1980-12-17</HIREDATE><SAL>800</SAL><DEPARTMENT><DNAME>RESEARCH</DNAME><LOC>DALLAS</LOC></DEPARTMENT></EMPLOYEE>

                I just want to display the tags in the below format if opened in notepad ( though its a .xml document user is opening the file in notepad )

                <EMPLOYEE>
                <ENAME>SMITH</ENAME>
                <JOB>CLERK</JOB>
                <MGR>7902</MGR>
                <HIREDATE>1980-12-17</HIREDATE>
                <SAL>800</SAL>
                <DEPARTMENT>
                <DNAME>RESEARCH</DNAME>
                <LOC>DALLAS</LOC>
                </DEPARTMENT>
                </EMPLOYEE>


                Thanks,
                S.

                Edited by: 983178 on Feb 19, 2013 1:15 PM
                • 5. Re: xml file opened in a notepad
                  Jason_(A_Non)
                  Did you follow the link I put on [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions252.htm#SQLRF06231]XMLSERIALIZE  above (and repeated here). Click it to go to the Oracle documentation as that should answer your question, depending upon your database version. Without knowing your DB version
                  select * from v$version
                  that is the best answer I will give. There is a way to do it before XMLSerialize came about, but it does not provide the same flexibility.

                  As for posting questions, one should follow these rules, even though this comes from the SQL * PL/SQL forum
                  {message:id=9360002}
                  • 6. Re: xml file opened in a notepad
                    user302631
                    Hello A_Non,

                    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
                    PL/SQL Release 10.2.0.5.0 - Production
                    CORE     10.2.0.5.0     Production
                    TNS for Solaris: Version 10.2.0.5.0 - Production
                    NLSRTL Version 10.2.0.5.0 - Production

                    I used xmlserialize it didn't work. Displaying in a single line when the file is opened in notepad.

                    SELECT xmlserialize( document XMLELEMENT("EMPLOYEE",
                    XMLFOREST( ename, job, mgr, hiredate, sal, comm) AS "EMP",
                    xmlforest( xmlforest( dname, loc)as "DEPARTMENT" )) )
                    from
                    emp e, dept d
                    where e.deptno = d.deptno;

                    Thanks,
                    S.

                    Edited by: 983178 on Feb 19, 2013 2:18 PM
                    • 7. Re: xml file opened in a notepad
                      odie_63
                      Hi,

                      Why don't you tell your users to open the files with a proper XML editor?

                      As said already, any recent web browser provides you with the functionality you seek, and much more.
                      I used xmlserialize it didn't work. Displaying in a single line when the file is opened in notepad.
                      XMLSerialize does not have the INDENT option in your version.

                      To pretty-print the output, use EXTRACT method :
                      SELECT  XMLELEMENT("EMPLOYEE",
                                   XMLFOREST( ename, job, mgr, hiredate, sal, comm) AS "EMP",           
                                  xmlforest( xmlforest( dname, loc)as "DEPARTMENT" )
                                ).extract('/*').getclobval()
                      from emp e, dept d
                      where e.deptno = d.deptno;
                      If you migrate later to a more recent version, the above won't work anymore, you'll have to use XMLSerialize then.
                      • 8. Re: xml file opened in a notepad
                        user302631
                        Thank a ton Odie.
                        This is working.
                        • 9. Re: xml file opened in a notepad
                          user302631
                          Odie,

                          This solution is working. But I started to notice ltl bit of performance issue.
                          It is taking longer than it used to and I need to display any where between 250k to 300k records.

                          Thanks.
                          • 10. Re: xml file opened in a notepad
                            jannybravo
                            If viewing the XML document is the only issue, the you can use the text editor Notepad++ and install the XMLTools plugin.
                            Using the XMLTools plugin you can easily format the XML document into a readable XML format.
                            Let me know if you need any more details.
                            • 11. Re: xml file opened in a notepad
                              user302631
                              Hi Jannybravo,

                              Thanks for the response.
                              I should have been more descriptive in the explaining the problem.

                              Performance issue is not with opening the file. It is with executing the procedure to retrieve the output file in xml format. Since I added .extract('/*')
                              i notice that the performance has gone down ltl bit.

                              Thanks again!
                              • 12. Re: xml file opened in a notepad
                                odie_63
                                983178 wrote:
                                I should have been more descriptive in the explaining the problem.

                                Performance issue is not with opening the file.
                                IMO, I think it is, on the contrary.
                                The root problem is to use the correct tool for the job.
                                i notice that the performance has gone down ltl bit.
                                Of course it has!

                                That's what all of us are trying to explain from the very beginning : do not pretty-print XML in the database. In your scenario, it's a task for an XML editor, client-side.

                                Adding meaningless whitespaces in the whole XML ought to take some more processing time, especially when the content grows bigger.
                                This "pretty-printing" behaviour is a performance bug, and that's one of the reason it's been removed in 11g.
                                • 13. Re: xml file opened in a notepad
                                  user302631
                                  Thank you Odie.

                                  I understand the reason for performance issue. Its hard to convince the customer to use other editors other than notepad because they are used to opening the file in notepad. When I tell the client that he cannot open the file with pretty print in notepad he is not going to be happy about it. Atleast I will try to convince him by providing the information. Are there any free xml editors that I can recommend to client to download.

                                  Thank again!
                                  • 14. Re: xml file opened in a notepad
                                    user302631
                                    Hello,

                                    I found a whole list under below link some are free and some are Proprietary.

                                    http://en.wikipedia.org/wiki/Comparison_of_XML_editors

                                    Is there a free editor that you can recommend?
                                    At any point user would open a file with a million records or more.

                                    Thank you!