OraFAQ Forum: SQL & PL/SQL » table record into XML user defined (2 merged)

Home » SQL & PL/SQL » SQL & PL/SQL » table record into XML user defined (2 merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

table record into XML user defined (2 merged) [message #682710]

Wed, 11 November 2020 05:53

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

Hello

I would like to know is it an efficient way to generate xml with own defined structure?
I mean generate xml from table record.

My XML code example:

<DOCUMENT xmlns="http://www.ness.com/tmobile/nonbilling">
  <HEADER>
    <LANGUAGE>EN</LANGUAGE>
    <CUSTOMERINFO>
      <WP_ID>M0001</WP_ID>
      <ID_CU>299999111113</ID_CU>  
      <ICO>123111123132</ICO>
      <DIC>321111321321</DIC>
      <BILLADDRESS>
        <ADDRESSLINE>Aaaa  Bbbbbbb</ADDRESSLINE>
        <ADDRESSLINE>Prerssss 13/296</ADDRESSLINE>
        <ADDRESSLINE>100 00 Town 10</ADDRESSLINE>
        <COUNTRY>CZ</COUNTRY>
      </BILLADDRESS>
      <CONTACTADDRESS>
        <ADDRESSLINE>Aaaa  Bbbbbbb</ADDRESSLINE>
        <ADDRESSLINE>Prerssss 13/296</ADDRESSLINE>
        <ADDRESSLINE>100 00 Town 10</ADDRESSLINE>
        <COUNTRY>CZ</COUNTRY>
      </CONTACTADDRESS>
      <EMAIL>jraasaapek@gmail.cz</EMAIL>   
    </CUSTOMERINFO>
    <OUTPUTTYPE>EMAIL</OUTPUTTYPE> <!-- LETTER/EMAIL/SMS -->    
    <CURRENCY>CZK</CURRENCY> 
  </HEADER>
  <BODY>
    <PROPERTY>
      <CODE>SU_ID</CODE> 
      <VALUE>1234111156789</VALUE>
    </PROPERTY>
 </BODY>
</DOCUMENT>  

Thanks 
Regards
Martin

Report message to a moderator

table record into XML user defined [message #682711 is a reply to message #682710]

Wed, 11 November 2020 05:54

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

Hello

I would like to know is it an efficient way to generate xml with own defined structure?
I mean generate xml from table record.

My XML code example:

<DOCUMENT xmlns="http://www.ness.com/tmobile/nonbilling">
  <HEADER>
    <LANGUAGE>EN</LANGUAGE>
    <CUSTOMERINFO>
      <WP_ID>M0001</WP_ID>
      <ID_CU>299999111113</ID_CU>  
      <ICO>123111123132</ICO>
      <DIC>321111321321</DIC>
      <BILLADDRESS>
        <ADDRESSLINE>Aaaa  Bbbbbbb</ADDRESSLINE>
        <ADDRESSLINE>Prerssss 13/296</ADDRESSLINE>
        <ADDRESSLINE>100 00 Town 10</ADDRESSLINE>
        <COUNTRY>CZ</COUNTRY>
      </BILLADDRESS>
      <CONTACTADDRESS>
        <ADDRESSLINE>Aaaa  Bbbbbbb</ADDRESSLINE>
        <ADDRESSLINE>Prerssss 13/296</ADDRESSLINE>
        <ADDRESSLINE>100 00 Town 10</ADDRESSLINE>
        <COUNTRY>CZ</COUNTRY>
      </CONTACTADDRESS>
      <EMAIL>jraasaapek@gmail.cz</EMAIL>   
    </CUSTOMERINFO>
    <OUTPUTTYPE>EMAIL</OUTPUTTYPE> <!-- LETTER/EMAIL/SMS -->    
    <CURRENCY>CZK</CURRENCY> 
  </HEADER>
  <BODY>
    <PROPERTY>
      <CODE>SU_ID</CODE> 
      <VALUE>1234111156789</VALUE>
    </PROPERTY>
 </BODY>
</DOCUMENT>  

Thanks 
Regards
Martin

Report message to a moderator

Re: table record into XML user defined [message #682712 is a reply to message #682710]

Wed, 11 November 2020 07:10

Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Something like that:

SQL> select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;
DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>
      <HIREDATE>12/17/1980 0:0:0</HIREDATE>
      <SAL>800</SAL>
      <DEPTNO>20</DEPTNO>
   </ROW>
   <ROW num="2">
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/20/1981 0:0:0</HIREDATE>
      <SAL>1600</SAL>
      <COMM>300</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
   <ROW num="3">
      <EMPNO>7521</EMPNO>
      <ENAME>WARD</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/22/1981 0:0:0</HIREDATE>
      <SAL>1250</SAL>
      <COMM>500</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
</ROWSET>

Report message to a moderator

Re: table record into XML user defined [message #682713 is a reply to message #682712]

Wed, 11 November 2020 07:19

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

But I need get the exact XML structure. The table has different structure.

Report message to a moderator

Re: table record into XML user defined [message #682714 is a reply to message #682712]

Wed, 11 November 2020 07:19

Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Or:

SQL> select xmlserialize(
  2           document
  3             xmlelement("EMPLOYEES",
  4                        xmlagg(xmlelement("EMPLOYEE",
  5                                          xmlforest(empno, ename))))
  6           indent size=2) res
  7  from emp
  8  where rownum <= 3
  9  /
RES
-------------------------------------------------------------------------
<EMPLOYEES>
  <EMPLOYEE>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
  </EMPLOYEE>
  <EMPLOYEE>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
  </EMPLOYEE>
  <EMPLOYEE>
    <EMPNO>7521</EMPNO>
    <ENAME>WARD</ENAME>
  </EMPLOYEE>
</EMPLOYEES>

[Updated on: Wed, 11 November 2020 07:23]

Report message to a moderator