This discussion is archived
5 Replies Latest reply: Dec 11, 2012 1:17 AM by Mindmap RSS

Is there any Tool to view / get XML format of SQL Query ?

Mindmap Pro
Currently Being Moderated
Hello,
Working on Oracle 11g R2 SOE,
This is my first try with XML function, suppose I have a query similar to the one mentioned here, How to get a well formatted XML file out of this query ??
I run it on SQL plus, and tried to "Spool" it into xml file, but all information was not formatted in XML.
select xmlelement(
         "DEPARTMENTS",
         xmlagg(
           xmlelement(
             "DEPARTMENT",
             xmlforest(
               d.deptno,
               d.dname,
               (
                 select xmlagg(
                          xmlelement(
                            "EMPLOYEE",
                             xmlforest(
                               e.empno,
                               xmlcdata(e.ename) ename,
                               e.hiredate
                             )
                           )
                        )
                 from   emp e
                 where  e.deptno = d.deptno and
                        e.empno in (7369, 7499, 7934)
               ) employees
             )
           )
         )
       ) x
from   dept d;
Regards,
Fateh
  • 1. Re: Is there any Tool to view / get XML format of SQL Query ?
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Fateh,
    Fateh wrote:
    Hello,
    Working on Oracle 11g R2 SOE,
    This is my first try with XML function, suppose I have a query similar to the one mentioned here, How to get a well formatted XML file out of this query ??
    That depends on what you mean by "well formatted". Always post the exact results you want.

    Maybe you want XMLSERIALIZE:
    select
           XMLSERIALIZE (DOCUMENT     -- *****  ADDED  *****
           xmlelement(
             "DEPARTMENTS",
             xmlagg(
               xmlelement(
                 "DEPARTMENT",
                 xmlforest(
                   d.deptno,
                   d.dname,
                   (
                     select xmlagg(
                              xmlelement(
                                "EMPLOYEE",
                                 xmlforest(
                                   e.empno,
                                   xmlcdata(e.ename) ename,
                                   e.hiredate
                                 )
                               )
                            )
                     from   emp e
                     where  e.deptno = d.deptno and
                            e.empno in (7369, 7499, 7934)
                   ) employees
                 )
               )
             )
           )
           )               -- *****  ADDED  *****
             x     
    from   dept d;
    This is just what you posted, only I added "XMLSERIALIZE (DOCUMENT" near the beginning, and a balancing ")" near the end.
    Output:
    X
    --------------------------------------------------------------------------------
    <DEPARTMENTS>
      <DEPARTMENT>
        <DEPTNO>10</DEPTNO>
        <DNAME>ACCOUNTING</DNAME>
        <EMPLOYEES>
          <EMPLOYEE>
            <EMPNO>7934</EMPNO>
            <ENAME><![CDATA[MILLER]]></ENAME>
            <HIREDATE>1982-01-23</HIREDATE>
          </EMPLOYEE>
        </EMPLOYEES>
      </DEPARTMENT>
      <DEPARTMENT>
        <DEPTNO>20</DEPTNO>
        <DNAME>RESEARCH</DNAME>
        <EMPLOYEES>
          <EMPLOYEE>
            <EMPNO>7369</EMPNO>
            <ENAME><![CDATA[SMITH]]></ENAME>
            <HIREDATE>1980-12-17</HIREDATE>
          </EMPLOYEE>
        </EMPLOYEES>
      </DEPARTMENT>
      <DEPARTMENT>
        <DEPTNO>30</DEPTNO>
        <DNAME>SALES</DNAME>
        <EMPLOYEES>
          <EMPLOYEE>
            <EMPNO>7499</EMPNO>
            <ENAME><![CDATA[ALLEN]]></ENAME>
            <HIREDATE>1981-02-20</HIREDATE>
          </EMPLOYEE>
        </EMPLOYEES>
      </DEPARTMENT>
      <DEPARTMENT>
        <DEPTNO>40</DEPTNO>
        <DNAME>OPERATIONS</DNAME>
      </DEPARTMENT>
    </DEPARTMENTS>
  • 2. Re: Is there any Tool to view / get XML format of SQL Query ?
    odie_63 Guru
    Currently Being Moderated
    I run it on SQL plus, and tried to "Spool" it into xml file, but all information was not formatted in XML.
    What you're doing is perfectly correct. It produces valid XML.

    As Frank said, you probably want pretty-printing ? In this case use XMLSerialize with INDENT option in Oracle 11, and EXTRACT in prior versions.

    However, you should not pretty-print the document tree unless you need to debug the output.
    Serializing with indentation adds both lots of unsignificant whitespaces (that could double the overall size in the worst case) and some overhead in the process.

    Edited by: odie_63 on 6 déc. 2012 06:52
  • 3. Re: Is there any Tool to view / get XML format of SQL Query ?
    Mindmap Pro
    Currently Being Moderated
    Thanks,
    Yes, I meant by well-XML-Formatted, the same format you got as the result of your Query.
    But, I run your query in SQL plus, SQL developer, APEX SQL Command and always get not well formatted result. I mean something similar to this:
    <DEPARTMENT ID="10" NAME="Administration"><EMPLOYEES><EMPLOYEE><ID>200</ID><NAME>Jennifer Whalen</NAME></EMPLOYEE></EMPLOYEES></DEPARTMENT>
    <DEPARTMENT ID="20" NAME="Marketing"><EMPLOYEES><EMPLOYEE><ID>201</ID><NAME>Michael Hartstein</NAME></EMPLOYEE><EMPLOYEE><ID>202</ID><NAME>Pat Fay</NAME></EMPLOYEE></EMPLOYEES></DEPARTMENT>
    What do I have to do in order to get the same as the format of your query result ?

    Best Regards,
    Fateh
  • 4. Re: Is there any Tool to view / get XML format of SQL Query ?
    BluShadow Guru Moderator
    Currently Being Moderated
    Fateh wrote:
    Thanks,
    Yes, I meant by well-XML-Formatted, the same format you got as the result of your Query.
    The point is that the phrase "well formatted" in XML terms, means an XML document that has all the correct opening and closing tags etc. and meets the XML standard. It does not mean one that is displayed in a pretty fashion with all the nice indenting of the hierarchy. That is called a "pretty printed" XML document.

    As Odie points out, for technical purposes you only need a well formatted XML document, because if you insist on using a pretty printed XML, then you are introducing a lot of white space into the document, which will make the XML content conisiderably larger e.g. larger files, or larger storage required. To programmatically deal with XML documents it does not need pretty printed XML at all.

    So, why do you need your data pretty printing?

    If you have the data stored in an XML file you can just open it in a web browser and it will present it pretty printed for you... if it's just a case of you wanting to be able to browse through the data for testing purposes etc.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points