6 Replies Latest reply on Apr 22, 2018 6:43 AM by Gaz in Oz

    XML output formatter

    mNem

      Formatting the xml output when working with xquery:

       

      Is there any feature or utility function available within any of the sql developer versions to auto format the xml output something like browsers do when displaying xml content.

       

      for example,

       

      From

       

      XMLOUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
      ---------------------------------------------------------
      <DEPTS><DEPT><ID>10</ID><N>Administration</N><M>200</M></DEPT></DEPTS>
      

       

      To

       

      XMLOUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
      ---------------------------------------------------------
      <DEPTS>
        <DEPT>
          <ID>10</ID>
          <N>Administration</N>
          <M>200</M>
        </DEPT>
      </DEPTS>
      
        • 1. Re: XML output formatter
          Gaz in Oz

          Use sqlplus:

          SQL> ed

          Wrote file afiedt.buf

           

            1  WITH xmldoc(xml) AS (

            2     SELECT XMLTYPE('<DEPTS><DEPT><ID>10</ID><N>Administration</N><M>200</M></DEPT></DEPTS>') FROM dual

            3  )

            4  SELECT xml

            5* FROM   xmldoc

          GAZ@xe:SQL+> /

           

          XML

          ------------------------------------------------------------

          <DEPTS>

            <DEPT>

              <ID>10</ID>

              <N>Administration</N>

              <M>200</M>

            </DEPT>

          </DEPTS>

           

          1 row selected.

           

          SQL>

          ...for example.

          • 2. Re: XML output formatter
            mNem

            Thanks Gaz in Oz.

             

            I rarely use sql plus. Even then, when I tried the below query in sqlplus, the result was not formatted.

             

            I am looking for a solution to use in the sql developer 4.1.5+.

             

             

            with departments (id, n, m) as 

            (

              select 10, 'Administration', 200 from dual union all

              select 20, 'Marketing',      250 from dual 

            )

            select

              xmlelement

              (

                depts, 

                xmlagg

                ( 

                  xmlelement

                  (

                    dept, xmlforest(id, n, m)

                  )

                ) 

              ) xmloutput

            from departments where id = 10

            • 3. Re: XML output formatter
              Gaz in Oz
              with departments (id, n, m) as (
                select 10, 'Administration', 200 from dual union all
                select 20, 'Marketing',      250 from dual
              )
              select xmlserialize(document xmlelement (
                        depts,
                        xmlagg (
                           xmlelement (
                              dept, xmlforest(id, n, m)
                           )
                        )
                     ) AS CLOB INDENT SIZE = 3) xmloutput
               from   departments where id = 10;
              
              XMLOUTPUT
              -------------------------------------------------------------
              <DEPTS>
                 <DEPT>
                    <ID>10</ID>
                    <N>Administration</N>
                    <M>200</M>
                 </DEPT>
              </DEPTS>
              
              
              

              XMLSERIALIZE()

              1 person found this helpful
              • 4. Re: XML output formatter
                mNem

                Hi Gaz in Oz,

                 

                Thanks. I am seeing some parts of the output getting truncated in both sql developer and sql plus when I use the clause AS CLOB. If VARCHAR2(appropriate size) is used then everything is fine.

                 

                 

                SQL> with departments (id, n, m) as (  
                  select 10, 'Administration', 200 from dual union all  
                  select 20, 'Marketing',      250 from dual  
                )  
                select 
                xmlserialize(document xmlelement (  
                          depts,  
                          xmlagg (  
                             xmlelement (  
                                dept, xmlforest(id, n, m)  
                             )  
                          )  
                       ) 
                       AS CLOB INDENT SIZE = 2)
                       xmloutput  
                 from   departments 
                 ;
                
                

                 

                XMLOUTPUT                                                 

                ------------------------------------------------------------

                <DEPTS>

                  <DEPT>

                    <ID>10</ID>

                    <N>Administration</N>

                    <M>200</M>

                  </DE

                 

                 

                SQL> with departments (id, n, m) as (  
                  select 10, 'Administration', 200 from dual union all  
                  select 20, 'Marketing',      250 from dual  
                )  
                select 
                xmlserialize(document xmlelement (  
                          depts,  
                          xmlagg (  
                             xmlelement (  
                                dept, xmlforest(id, n, m)  
                             )  
                          )  
                       ) 
                       AS CLOB INDENT SIZE = 6)
                       xmloutput  
                 from   departments 
                 ;
                

                 

                XMLOUTPUT                                                 

                ------------------------------------------------------------

                <DEPTS>

                      <DEPT>

                            <ID>10</ID>

                            <N>Administration</N>

                 

                 

                 

                SQL> with departments (id, n, m) as (  
                  select 10, 'Administration', 200 from dual union all  
                  select 20, 'Marketing',      250 from dual  
                )  
                select 
                xmlserialize(document xmlelement (  
                          depts,  
                          xmlagg (  
                             xmlelement (  
                                dept, xmlforest(id, n, m)  
                             )  
                          )  
                       ) 
                       AS VARCHAR2(200) INDENT SIZE = 3)
                       xmloutput  
                 from   departments 
                 ;
                

                 

                XMLOUTPUT                                                 

                ------------------------------------------------------------

                <DEPTS>                                                    

                   <DEPT>                                                  

                      <ID>10</ID>                                          

                      <N>Administration</N>                                

                      <M>200</M>                                           

                   </DEPT>                                                 

                   <DEPT>                                                  

                      <ID>20</ID>                                          

                      <N>Marketing</N>                                     

                      <M>250</M>                                           

                   </DEPT>                                                 

                </DEPTS>

                • 5. Re: XML output formatter
                  Gaz in Oz

                  You need to "set long <a large number> for example, in sqlplus, the defalut length is 80.

                  SQL> show long

                  80

                  SQL> /

                   

                  XMLOUTPUT

                  --------------------------------------------------------------------------------

                  <DEPTS>

                    <DEPT>

                        <ID>10</ID>

                        <N>Administration</N>

                        <M>200</M>

                   

                  SQL> set long 50000

                  SQL> /

                   

                  XMLOUTPUT

                  --------------------------------------------------------------------------------

                  <DEPTS>

                    <DEPT>

                        <ID>10</ID>

                        <N>Administration</N>

                        <M>200</M>

                    </DEPT>

                  </DEPTS>

                  • 6. Re: XML output formatter
                    Gaz in Oz

                    ...sqldeveloper 17.4.1.054.0712, works ok too:

                    1 person found this helpful