XML formater

On Oracle 10g (or even 9i)

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

Thanks
LVL 1
peledcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.