Home » SQL & PL/SQL » SQL & PL/SQL » table record into XML user defined (2 merged)
table record into XML user defined (2 merged) [message #682710] Wed, 11 November 2020 05:53 Go to next message
mape
Messages: 280
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
table record into XML user defined [message #682711 is a reply to message #682710] Wed, 11 November 2020 05:54 Go to previous messageGo to next message
mape
Messages: 280
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
Re: table record into XML user defined [message #682712 is a reply to message #682710] Wed, 11 November 2020 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
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>
Re: table record into XML user defined [message #682713 is a reply to message #682712] Wed, 11 November 2020 07:19 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member

But I need get the exact XML structure. The table has different structure.
Re: table record into XML user defined [message #682714 is a reply to message #682712] Wed, 11 November 2020 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
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

Re: table record into XML user defined [message #682715 is a reply to message #682714] Wed, 11 November 2020 07:26 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member

I need to insert some records from table into the exact XML structure.
Re: table record into XML user defined [message #682716 is a reply to message #682715] Wed, 11 November 2020 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Wed, 26 August 2020 12:26

This is clear as mud.
Provide a test case with its result.

Re: table record into XML user defined [message #682717 is a reply to message #682716] Wed, 11 November 2020 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read XML Functions.

Re: table record into XML user defined [message #682718 is a reply to message #682717] Wed, 11 November 2020 08:14 Go to previous message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
Thanks for help. The last one example really help me.
Previous Topic: OLAP sql-cube operator
Next Topic: table refresh process - discuss optoins
Goto Forum:
  


Current Time: Sun May 16 14:18:00 CDT 2021