Solved

XML formater

Posted on 2004-10-22
1,032 Views
Last Modified: 2012-08-14
On Oracle 10g (or even 9i)

Is the an easy way to format XML out of normalized tables?

Thanks
0
Question by:peledc
    3 Comments
     
    LVL 75

    Assisted Solution

    by:slightwv (䄆 Netminder)
    In short:  Yes.

    Take a look at the sql functions: xmlelement, xmlforest, etc...

    There are also PL/SQL packages for this: xmlquery

    There is also full DOM support.

    If you can provide more specifics, I'm sure myself or someone can provide a working sample.  We would need, at a minimum, a table structure and desired XML.  Some sample data would also be nice.
    0
     
    LVL 47

    Assisted Solution

    by:schwertner
    Create XML from table:
    -- 9i version

          connect scott/tiger
          set serveroutput on
          DECLARE
                  Ctx    DBMS_XMLGEN.ctxHandle;   -- Var's to convert SQL output to XML
                  xml    clob;
                  emp_no NUMBER := 7369;

                  xmlc   varchar2(4000);          -- Var's required to convert lob to varchar
                  off    integer := 1;
                  len    integer := 4000;
          BEGIN
                  Ctx := DBMS_XMLGEN.newContext('SELECT * FROM emp WHERE empno = '||emp_no);
                  xml := DBMS_XMLGEN.getXML(Ctx);
                  DBMS_XMLGEN.closeContext(Ctx);
          
                  DBMS_LOB.READ(xml, len, off, xmlc);   -- Display first part on screen
                  DBMS_OUTPUT.PUT_LINE(xmlc);
          END;
          /

    ----Working 9.0.2------------------------------------------------------
    connect scott/tiger
    set serveroutput on
    DECLARE
          Ctx    DBMS_XMLGEN.ctxHandle;   -- Var's to convert SQL output to XML
          xml    clob;
          emp_no NUMBER := 7369;

          xmlc   varchar2(4000);          -- Var's required to convert lob to varchar
          off    integer := 1;
          len    integer := 4000;
    BEGIN
          Ctx := DBMS_XMLGEN.newContext('SELECT * FROM emp WHERE empno = '||emp_no);
          xml := DBMS_XMLGEN.getXML(Ctx);
          DBMS_XMLGEN.closeContext(Ctx);      
          DBMS_LOB.READ(xml, len, off, xmlc);   -- Display first part on screen
            DBMS_OUTPUT.ENABLE(5000);
          DBMS_OUTPUT.PUT_LINE(xmlc);
          END;
    /


    <?xml version="1.0"?>
    <ROWSET>
     <ROW>
      <EMPNO>7369</EMPNO>

    <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>

    <HIREDATE>17-DEC-80</HIREDATE>
      <SAL>800</SAL>
      <DEPTNO>20</DEPTNO>

    </ROW>
    </ROWSET>


    PL/SQL procedure successfully completed.


     

     

    connect scott/tiger
    set serveroutput on
    DECLARE
          Ctx    DBMS_XMLGEN.ctxHandle;   -- Var's to convert SQL output to XML
          xml    clob;
          emp_no NUMBER := 7369;
            lob_l  integer := 0;
            cnt  integer := 1;
          xmlc   varchar2(4000);          -- Var's required to convert lob to varchar
          off    integer := 1;
          len    integer := 4000;
            buf    varchar2(255);
    BEGIN
          Ctx := DBMS_XMLGEN.newContext('SELECT empno as EMPLOYEE_NUMBER,
                                                  ename as EMPLOYEE_NAME,
                                                  job   as JOB_TITLE,
                                                  mgr   as MANAGER,
                                                  hiredate,
                                                  sal    as SALARY,
                                                  deptno as DEPARTMENT_NUMBER
                                           FROM emp WHERE empno = '||emp_no);
          xml := DBMS_XMLGEN.getXML(Ctx);
          DBMS_XMLGEN.closeContext(Ctx);
            lob_l := DBMS_LOB.GETLENGTH (xml);      
          DBMS_LOB.READ(xml, len, off, xmlc);   -- Display first part on screen
            DBMS_OUTPUT.ENABLE(5000);
            IF length(xmlc) <= 255 THEN
               DBMS_OUTPUT.PUT_LINE(xmlc);
            ELSE
               cnt := 1;
               WHILE cnt < length(xmlc) LOOP
                  IF (length(xmlc) - cnt +1) > 255 THEN
                     buf := SUBSTR(xmlc,cnt,255);
                     DBMS_OUTPUT.PUT_LINE(buf);
                     cnt := cnt + 255;
                  ELSE
                     buf := SUBSTR(xmlc,cnt,length(xmlc)-cnt+1);
                     DBMS_OUTPUT.PUT_LINE(buf);
                     cnt := length(xmlc);
                  END IF;      
                END LOOP;
            END IF;
          END;
    /

    <?xml version="1.0"?>
    <ROWSET>
     <ROW>
      <EMPLOYEE_NUMBER>7369</EMPLOYEE_NUMBER>
      <EMPLOYEE_NAME>SMITH</EMPLOYEE_NAME>
      <JOB_TITLE>CLERK</JOB_TITLE>
      <MANAGER>7902</MANAGER>
      <HIREDATE>17-DEC-80</HIREDATE>
      <SALARY>800</SALARY>
      <DEPARTMENT_NUMBER>20
    </DEPARTMENT_NUMBER>
     </ROW>
    </ROWSET>


    connect lsuser/lsuser@test
    set serveroutput on
    DECLARE
          Ctx    DBMS_XMLGEN.ctxHandle;   -- Var's to convert SQL output to XML
          xml    clob;
          emp_no NUMBER := 7369;
            lob_l  integer:= 0;
            cnt  integer := 1;
          xmlc   varchar2(32000);          -- Var's required to convert lob to varchar
          off    integer := 1;
          len    integer := 4000;
            buf    varchar2(255);
    BEGIN
          Ctx := DBMS_XMLGEN.newContext('SELECT *
                                           FROM temperature_current');
          xml := DBMS_XMLGEN.getXML(Ctx);
          DBMS_XMLGEN.closeContext(Ctx);
            lob_l := DBMS_LOB.GETLENGTH (xml);      
          DBMS_LOB.READ(xml, len, off, xmlc);   -- Display first part on screen
            DBMS_OUTPUT.ENABLE(50000);
            IF length(xmlc) <= 200 THEN
               DBMS_OUTPUT.PUT_LINE(xmlc);
            ELSE
               cnt := 1;
               WHILE cnt < length(xmlc) LOOP
                  IF (length(xmlc) - cnt +1) > 200 THEN
                     buf := SUBSTR(xmlc,cnt,200);
                     DBMS_OUTPUT.PUT_LINE(buf);
                     cnt := cnt + 200;
                  ELSE
                     buf := SUBSTR(xmlc,cnt,length(xmlc)-cnt+1);
                     DBMS_OUTPUT.PUT_LINE(buf);
                     dbms_output.new_line;
                     cnt := length(xmlc);
                  END IF;      
                END LOOP;
            END IF;
          END;
    /

    <?xml version="1.0"?>
    <ROWSET>
     <ROW>

    <IDENTIFIER>C09A374430D3251099AE8090246D9300</IDENTIFIER>

    <VERSION>1</VERSION>
      <DELETED> </DELETED>

    <CREATEUSER>114B88F0D51C2510955F8090246D9300</CREATEUS
    ER>
      <CREATEDATE>1064677839468</CREATEDATE>

    <CREATETIMEZONE>7200000</CREATETIMEZONE>

    <CHANGEUSER>114B88F0D51C2510955F8090246D9300</CHANGEUSER>

    <CHANGEDATE>1067936549968</CHANGEDATE>
      <CHANGETI
    MEZONE>-1</CHANGETIMEZONE>
      <NOTE>löp</NOTE>

    <OWNER>114B88F0D51C2510955F8090246D9300</OWNER>

    <TIMEOFMEASURE>1064677800000</TIMEOFMEASURE>

    <TEMPERATUREVALUE>37</TEMPERATUREVALUE>
      <TEMPERATUREU
    NIT>UNT0100020</TEMPERATUREUNIT>
      <LOCATIONOFMEASURE>01</LOCATIONOFMEASURE>

    <MDROLE>P</MDROLE>
      <PACKAGE>null</PACKAGE>
     </ROW>
     <ROW>

    <IDENTIFIER>807F526220DD251099AE8090246D9300</IDENTIFIER>

    <VERSION>2</VERSION>
      <DELETED>X</DELETED>

    <CREATEUSER>114B88F0D51C2510955F8090246D9300</CREATEUSER>

    <CREATEDATE>1065770529656</CREATEDATE>
      <CREATETIMEZONE>0</CREATETIMEZONE>

    <CHANGEUSER>114
    B88F0D51C2510955F8090246D9300</CHANGEUSER>

    <CHANGEDATE>1066138499562</CHANGEDATE>
      <CHANGETIMEZONE>-1</CHANGETIMEZONE>

    <NOTE>dd</NOTE>
      <OWNER>114B88F0D51C2510955F8090246D9300</OWNER>
      <TIMEOFM
    EASURE>1065770520000</TIMEOFMEASURE>
      <TEMPERATUREVALUE>37</TEMPERATUREVALUE>

    <TEMPERATUREUNIT>UNT0100020</TEMPERATUREUNIT>

    <LOCATIONOFMEASURE>01</LOCATIONOFMEASURE>
      <MDROLE>P</MDROLE>
      <PACKA
    GE>null</PACKAGE>
     </ROW>
     <ROW>

    <IDENTIFIER>1023C1DE45E4251099AE8090246D9300</IDENTIFIER>

    <VERSION>4</VERSION>
      <DELETED> </DELETED>

    <CREATEUSER>114B88F0D51C2510955F8090246D9300</CREATEUSER>

    <CREATEDATE>1066556287921</CREATEDATE>
      <CREATETIMEZONE>0</CREATETIMEZONE>

    <CHANGEUSER>114B88F0D51C2510955F8090246D9300</CHANGEUSER>

    <CHANGEDATE>1067937278546</CHANGEDATE>
      <CHANGETIMEZONE>-1</
    CHANGETIMEZONE>
      <NOTE>¿¿¿¿¿¿¿¿</NOTE>

    <OWNER>114B88F0D51C2510955F8090246D9300</OWNER>

    <TIMEOFMEASURE>1066556220000</TIMEOFMEASURE>

    <TEMPERATUREVALUE>35</TEMPERATUREVALUE>
      <TEMPERATUREUNIT>UN
    T0100020</TEMPERATUREUNIT>
      <LOCATIONOFMEASURE>01</LOCATIONOFMEASURE>

    <MDROLE>P</MDROLE>
      <PACKAGE>null</PACKAGE>
     </ROW>
     <ROW>

    <IDENTIFIER>A073966E20DD251099AE8090246D9300</IDENTIFIER>
      <VERS
    ION>1</VERSION>
      <DELETED>X</DELETED>

    <CREATEUSER>114B88F0D51C2510955F8090246D9300</CREATEUSER>

    <CREATEDATE>1065770550234</CREATEDATE>
      <CREATETIMEZONE>0</CREATETIMEZONE>

    <CHANGEUSER>114B88F0D
    51C2510955F8090246D9300</CHANGEUSER>
      <CHANGEDATE>1066138504781</CHANGEDATE>

    <CHANGETIMEZONE>-1</CHANGETIMEZONE>

    <OWNER>114B88F0D51C2510955F8090246D9300</OWNER>

    <TIMEOFMEASURE>1065770520000</TI
    MEOFMEASURE>
      <TEMPERATUREVALUE>36</TEMPERATUREVALUE>

    <TEMPERATUREUNIT>UNT0100020</TEMPERATUREUNIT>

    <LOCATIONOFMEASURE>01</LOCATIONOFMEASURE>
      <MDROLE>P</MDROLE>
     </ROW>
     <ROW>

    <IDENTIFIER>506
    8449B483B25109C298090246D9300</IDENTIFIER>
      <VERSION>1</VERSION>

    <DELETED>X</DELETED>

    <CREATEUSER>114B88F0D51C2510955F8090246D9300</CREATEUSER>

    <CREATEDATE>1047975716693</CREATEDATE>
      <CREATET
    IMEZONE>0</CREATETIMEZONE>

    <CHANGEUSER>114B88F0D51C2510955F8090246D9300</CHANGEUSER>

    <CHANGEDATE>1047988874824</CHANGEDATE>
      <CHANGETIMEZONE>0</CHANGETIMEZONE>

    <OWNER>114B88F0D51C2510955F809024
    6D9300</OWNER>
      <TIMEOFMEASURE>1047975660000</TIMEOFMEASURE>

    <TEMPERATUREVALUE>34.8</TEMPERATUREVALUE>

    <TEMPERATUREUNIT>UNT0100020</TEMPERATUREUNIT>

    <LOCATIONOFMEASURE>01</LOCATIONOFMEASURE>

    <MDROLE>P</MDROLE>
     </ROW>
     <ROW>

    <IDENTIFIER>10A06B1B683B25109C298090246D9300</IDENTIFIER>

    <VERSION>17</VERSION>
      <DELETED>X</DELETED>

    <CREATEUSER>114B88F0D51C2510955F8090246D9300</CREATEUSER>

      <CREATEDATE>1047989246097</CREATEDATE>
      <CREATETIMEZONE>0</CREATETIMEZONE>

    <CHANGEUSER>114B88F0D51C2510955F8090246D9300</CHANGEUSER>

    <CHANGEDATE>1063301027250</CHANGEDATE>
      <CHANGETIMEZONE>0<
    /CHANGETIMEZONE>
      <NOTE>hhhrrrr jjj </NOTE>

    <OWNER>114B88F0D51C2510955F8090246D9300</OWNER>

    <TIMEOFMEASURE>1063883220000</TIMEOFMEASURE>

    <TEMPERATUREVALUE>66</TEMPERATUREVALUE>
      <TEMPERATUREUN

    PL/SQL procedure successfully completed.

    SQL>

    0
     
    LVL 75

    Accepted Solution

    by:
    Here's an emp example w/o PL/SQL:
    ========================
    select m.myVersion.extract('//myXML')
    from
    (
    --above this is to make it pretty for sqlplus viewing
    select XMLElement("myXML",
          (
          select xmlagg(
                xmlelement("employeeInfo",
                      xmlattributes( EMPNO as "EmployeeNumber"),
                      (
                            xmlforest(
                            ENAME as "EmployeeName",
                            JOB as "EmployeeJob"
                            )
                      )
                )
                )
          from emp
          )
    ) myVersion
    from dual
    --below this is to make it pretty for sqlplus viewing
    ) m
    ;

    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now