Home » Developer & Programmer » JDeveloper, Java & XML » What are the options for getting data in XML format using SQL queries? (11.2.0.4.0 on Linux)
What are the options for getting data in XML format using SQL queries? [message #639250] Fri, 03 July 2015 07:06 Go to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello

We have Oracle database tables having datatypes as varchar2, number, date, timestamp (and Not XMLTYPE)

And we want to extract this data in XML format which would be then used by SOA services

I am using SQL functions like XELEMENT, XMLFOREST, XMLAGG etc.

Due to specific requirements we want to abstract the complexity of the Queries for which we are creating view over the queries retieving data from database table
such as - 
CREATE or REPLACE VIEW v_data as 
     SELECT XMLElement("DEPARTMENT"
                     , XMLAttributes( deptno as "ID"
					 ................
					 ................



I referred the link - http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm#ADXDB1600

And from it I understand following 2 options can't be used in my case

1) DBMS_XMLGEN - this would return the result as CLOB whereas I want XML output similar to what XMLELEMENT returns

2) SYS_XMLGEN and SYS_XMLAGG - this takes single argument and won't be useful in my case

Could anybody please suggest other options available?

Thanks and Regards
Orapratap

Report message to a moderator

Re: What are the options for getting data in XML format using SQL queries? [message #639254 is a reply to message #639250] Fri, 03 July 2015 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I think DBMS_XMLQUERY will do the trick for your case:
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: What are the options for getting data in XML format using SQL queries? [message #639255 is a reply to message #639254] Fri, 03 July 2015 10:49 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Michel

Many thanks for your reply

I am not much familiar to XML things and have confusion over options for - querying tables containing (varchar2, number, date etc data ) v/s querying tables containing XML as data

As I mentioned in my earlier post we would be querying tables containing varchar2/number/date/timestamp data

In line with your reply I have following queries

1)
Considering that I need XML output to be sent to SOA do we say
This
create view v4 as select xmltype(dbms_xmlquery.getxml('select * from emp where rownum <= 3')) data from dual;

is better than following?
create view v5 as select xmltype(dbms_xmlgen.getxml('select * from emp where rownum <= 3')) data from dual;


2)
By nesting SQL functions XMLELEMENT, XMLAGG, XMLFOREST we get Nested structure(nested tags)

For dbms_xmlgen or dbms_xmlquery I understand we would get nested structure only by using CAST+ MULTISET which in turn can have Performance impact; right?

3)
We are thinking of using following logic
1) use sql/pl-sql involving logic to get XML output from denormalized table
2) create view over sql/pl-sql stuff in point 1
3) query the view as created in step 2

considering we would be querying number of tables would the following work?
1) Create XML schema definition
2) Create XMLType view using SQL functions and the xsd in point 1
3) Query the view as created in point 2


Thanks in advance

Kind Regards
Orapratap

Report message to a moderator

Re: What are the options for getting data in XML format using SQL queries? [message #639257 is a reply to message #639255] Fri, 03 July 2015 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

1) I don't know anything about SOA but I'm sure you don't need to cast the result to XMLTYPE and can return the string; the purpose of casting a string to XMLTYPE is to 1) validate the string is a valid XML one and 2) pass it to an Oracle XML function. As for the one you have to use, choose the one which returns the result you want:
SQL> select dbms_xmlquery.getxml('select * from emp where rownum <= 3') data from dual;
DATA
---------------------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>
      <HIREDATE>1980-12-17 00:00:00</HIREDATE>
      <SAL>800</SAL>
      <DEPTNO>20</DEPTNO>
   </ROW>
   <ROW num="2">
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>1981-02-20 00:00:00</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>1981-02-22 00:00:00</HIREDATE>
      <SAL>1250</SAL>
      <COMM>500</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
</ROWSET>

1 row selected.

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

1 row selected.


2) I don't know, it depends on what result you want to have from what you want to get it, if there are multiple tools this is because each one is better (easier, faster, any criteria you want) for some cases. And one more: XMLTABLE.
You can get nested structure with DBMS_XMLQUERY too.
Example


3) The main point is stay with SQL if you can do it in plain SQL.

Report message to a moderator

Re: What are the options for getting data in XML format using SQL queries? [message #639713 is a reply to message #639250] Tue, 14 July 2015 10:37 Go to previous message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Michel

Many Thanks for your detailed reply

..and sorry for my delayed reply

Regarding your reply on point No. 2, using CURSOR in the manner demonstrated by you would give output as required but again I think at the cost of performance implication

Regarding point No.3 please let me elaborate

I need Following XML output

Listing-1
--------------------
<?xml version="1.0" encoding="UTF-8"?>
<company companyid="1001"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="shiporder.xsd">
  <branch branchid="1001"
    <department>
      <deptno>10</deptno >
      <dname>Marketing</dname >
          <employee>
             <empno>7369</empno>
             <ename> Green Smith </ename>
             <salary>9000</salary >
          </employee>
          <employee>
             <empno>7890</empno>
             <ename>Jack Mason</ename>
             <salary>5000</salary >
        </ employee>
  </department>
  <department>
      <deptno>20</deptno >
      <dname>Purchase</dname >
          <employee>
             <empno>8679</empno>
             <ename> Mike Lamb </ename>
             <salary>8000</salary >
          </employee>
          <employee>
             <empno>5678</empno>
             <ename>Ian Whitesmith</ename>
             <salary>7000</salary >
        </ employee>
  </department>
  </branch>
</company>


For this I create following XML Schema definition

Listing-2
--------------------
<?xml version="1.0" encoding="UTF-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="company">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="branch">
           <xs:complexType>
	<xs:sequence>
	    <xs:element name="department">
	       <xs:complexType>
	           <xs:sequence>
		  <xs:element name="deptno" type="xs:positiveInteger "/>
		  <xs:element name="dname" type="xs:string"/>
		         <xs:element name="employee" maxOccurs="unbounded">
		              <xs:complexType>
			   <xs:sequence>
			      <xs:element name="empno" type="xs:string"/>
	                                   <xs:element name="ename" type="xs:string" minOccurs="0"/>
			      <xs:element name="salary" type="xs:positiveInteger"/>
			   </xs:sequence>
			</xs:complexType>
		          </xs:element>
	          </xs:sequence>
	      </xs:complexType>
	  </xs:element>
	</xs:sequence>
            <xs:attribute name="branchid" type="xs:string" use="required"/>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="companyid" type="xs:string" use="required"/>
   </xs:complexType>
  </xs:element>
</xs:schema>


Now using the following query on a view I want to generate XML output as in Listing-1 which would use Schema definition as mentioned in Listing-2 above

select * from v_emp_data;


Could you please suggest on this?

I tried checking options like XMLType view etc. but seems that's not option to serve above purpose

Thanks and Regards
Orapratap

Report message to a moderator

Previous Topic: Help adding more fields in XML?
Next Topic: How to parse complex empty elements
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Feb 03 17:45:30 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.