• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1038
  • Last Modified:

XML formater

On Oracle 10g (or even 9i)

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

Thanks
0
peledc
Asked:
peledc
  • 2
3 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
schwertnerCommented:
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
 
slightwv (䄆 Netminder) Commented:
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now