Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

XML formater

Posted on 2004-10-22
3
Medium Priority
?
1,037 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
Comment
Question by:peledc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 668 total points
ID: 12380256
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 48

Assisted Solution

by:schwertner
schwertner earned 332 total points
ID: 12381682
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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 668 total points
ID: 12382304
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

618 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