Home » Developer & Programmer » JDeveloper, Java & XML » XML query with union (Oracle 9i, 10g, 11g)
XML query with union [message #391463] Thu, 12 March 2009 05:43 Go to next message
andy_9
Messages: 3
Registered: March 2009
Junior Member
Hi,

I need to get the following query in xml:
select * from dba_stmt_audit_opts union select * from dba_priv_audit_opts;

xml schema is:
<xsd:complexType name="sec_tauditing">
<xsd:sequence>
<xsd:element name="sec_audit" type="xsd:string" maxOccurs="unbounded" />
</xsd:sequence>
<xsd:attribute name="sec_audit_option" type="xsd:string" use="required" />
<xsd:attribute name="sec_auditing_user_name" type="xsd:string" use="required" />
<xsd:attribute name="sec_auditing_proxy" type="xsd:string"/>
<xsd:attribute name="sec_auditing_success" type="xsd:string" use="required" />
<xsd:attribute name="sec_auditing_failure" type="xsd:string" use="required" />
</xsd:complexType>

This is my approach but I am getting a ora-01427: single row subquery returns more than one row

SELECT xmlelement ("sec_auditing",
xmlagg((
select xmlelement("sec_audit",
xmlattributes (audit_option as "sec_audit_option", user_name AS "sec_auditing_user_name", proxy_name AS "sec_auditing_proxy", success AS "sec_auditing_success", failure AS "sec_auditing_failure"))
FROM dba_stmt_audit_opts
union all
select xmlelement("sec_audit",
xmlattributes (privilege as "sec_audit_option", user_name AS "sec_auditing_user_name", proxy_name AS "sec_auditing_proxy", success AS "sec_auditing_success", failure AS "sec_auditing_failure"))
from dba_priv_audit_opts))) from dual
/


Does someone have a clue how to get that working?

Thanks!
Re: XML query with union [message #391640 is a reply to message #391463] Thu, 12 March 2009 15:06 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hi,

One way would be to us an inline view (note, pseudo-code only, but you should get the idea) :

SELECT XMLELEMENT("sec_auditing",
         XMLAGG(XMLELEMENT("sec_audit",
                  XMLATTRIBUTES(audit_option  "sec_audit_option")
               )
       )
FROM 
( 
  select 
    audit_option sec_audit_option
    from dba_stmt_audit_opts 
   union
  select 
    privilege
    from dba_priv_audit_opts 
) x
/
Re: XML query with union [message #391645 is a reply to message #391640] Thu, 12 March 2009 15:49 Go to previous messageGo to next message
andy_9
Messages: 3
Registered: March 2009
Junior Member
So, you mean like that:

SELECT xmlelement ("sec_auditing",
xmlagg(
select xmlelement("sec_audit",
xmlattributes (x.audit_option as "sec_audit_option", x.user_name AS "sec_auditing_user_name", x.proxy_name AS "sec_auditing_proxy", x.success AS "sec_auditing_success", x.failure AS "sec_auditing_failure"))
FROM
(
select
*
from dba_stmt_audit_opts
union
select
*
from dba_priv_audit_opts
))) x
/

Gives me missing expression in line 3 "select xmlelement ("sec_audit"...)

Don't know why. Probably something with the parenthesis. Clue?

Thanks for your help! Appreciate that!
Re: XML query with union [message #391650 is a reply to message #391463] Thu, 12 March 2009 16:07 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hi,

You don't need the second select, XMLAGG can take an XMLELEMENT directly, as in the above example, plus only select the columns you want and alias them to the same name.
Re: XML query with union [message #391654 is a reply to message #391463] Thu, 12 March 2009 16:21 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Okay, this should work. Unless I've misread your XSD Wink

SELECT 
  XMLELEMENT("sec_auditing",
    XMLAGG(XMLELEMENT("sec_audit",
             XMLATTRIBUTES(x.sec_audit_option   "sec_audit_option",
                           x.user_name          "sec_auditing_user_name")
                     )
          )
  )
FROM 
  (
    SELECT audit_option  sec_audit_option,
           user_name
      FROM dba_stmt_audit_opts 
     UNION 
    SELECT privilege,
           user_name
      FROM dba_priv_audit_opts
  ) x
/
Re: XML query with union [message #391655 is a reply to message #391654] Thu, 12 March 2009 16:51 Go to previous message
andy_9
Messages: 3
Registered: March 2009
Junior Member
hm...looks good to me but get an error:

SELECT xmlelement ("sec_auditing",
xmlagg(
xmlelement("sec_audit",
xmlattributes (x.sec_audit_option as "sec_audit_option", x.sec_auditing_user_name AS "sec_auditing_user_name", x.sec_auditing_proxy AS "sec_auditing_proxy", x.sec_auditing_success AS "sec_auditing_success", x.sec_auditing_failure AS "sec_auditing_failure"))))
FROM
(
select
audit_option as "sec_audit_option", user_name AS "sec_auditing_user_name", proxy_name AS "sec_auditing_proxy", success AS "sec_auditing_success", failure AS "sec_auditing_failure"
from dba_stmt_audit_opts
union
select
privilege, user_name, proxy_name, success, failure
from dba_priv_audit_opts
) x
/


error at line 4
ora-00904: x.sec_auditing_failure: invalid identifier

Failure is the correct column name in dba_stmt_audit_opts and sec_auditing_failure should be ok for the xml-select. Looks strange to me. It'll give me an error for all of the columns btw.
Previous Topic: Java console access (oracle 9ias running on unix)
Next Topic: Load XML file to Oracle
Goto Forum:
  


Current Time: Fri Aug 22 10:57:53 CDT 2014

Total time taken to generate the page: 0.06952 seconds