Database Software

I am fiddling with a question from a colleague, which I thought is a good exercise. While using the latest database version I am trying to add namespaces to an XML document, "the XMLDB way".

My colleague needs this because he is working with object types is the context of using Oracle Enterprise Service Bus / SOA Suit. He needs to convert object types to XML but WITH namespace declarations.
I want to solve it, for example via XQuery, but am a bit stuck.

TEST CASE

SQL> CREATE OR REPLACE TYPE PAF_FLOW_EVENT_T AS OBJECT
  2   (  proces_id            number
  3   ,  proces_type          varchar2(30)
  4   ,  proces_name          varchar2(100)
  5   ,  proces_aanmaakdatum  date
  6    ,  event                varchar2(30)
  7   ,  os_user              varchar2(50)
  8   ,  betrokkene_id        number
  9    ,  begunstigde_id       number
 10    ,  opmerking            varchar2(2000));

Type created.

SQL> create or replace
  2  function paf
  3  return PAF_FLOW_EVENT_T
  4  as
  5  begin
  6    return PAF_FLOW_EVENT_T( 1, 'ProcessType','ProcessName',sysdate,'EventName','OSUser',999,666,'Remark'
  7                     );
  8  end;
  9 /

Function created.

SQL> select paf from dual;

PAF(PROCES_ID, PROCES_TYPE, PROCES_NAME, PROCES_AANMAAKDATUM, EVENT, OS_USER, BE
--------------------------------------------------------------------------------
PAF_FLOW_EVENT_T(1, 'ProcessType', 'ProcessName', '03-SEP-10', 'EventName', 'OSU
ser', 999, 666, 'Remark')


1 row selected.

SQL> set long 10000
SQL> set pages 5000
SQL> set feed on

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3          $i/*
  4          '
  5          passing xmltype(paf) as "i"
  6          returning content
  7         )
  8          as "XMLQuery Output"
  9 from  dual;

XMLQuery Output
--------------------------------------------------------------------------------
<PAF_FLOW_EVENT_T><PROCES_ID>1</PROCES_ID><PROCES_TYPE>ProcessType</PROCES_TYPE><PROCES_NAME>ProcessName</PROCES_NAME><PROCES_AANMAAKDATUM>03-SEP-10</PROCES_AANMAAKDATUM><EVENT>EventName</EVENT><OS_USER>OSUser</OS_USER><BETROKKENE_ID>999</BETROKKENE_ID><BEGUNSTIGDE_ID>666</BEGUNSTIGDE_ID><OPMERKING>Remark</OPMERKING></PAF_FLOW_EVENT_T>

1 row selected.

Output needed (without the pretty print) would be

XMLQuery Output
--------------------------------------------------------------------------------
<PAF_FLOW_EVENT_T xmlns="http://www.domainname.nl/pas/events/">
  <PROCES_ID>1</PROCES_ID>
  <PROCES_TYPE>ProcessType</PROCES_TYPE>
  <PROCES_NAME>ProcessName</PROCES_NAME>
  <PROCES_AANMAAKDATUM>03-SEP-10</PROCES_AANMAAKDATUM>
  <EVENT>EventName</EVENT>
  <OS_USER>OSUser</OS_USER>
  <BETROKKENE_ID>999</BETROKKENE_ID>
  <BEGUNSTIGDE_ID>666</BEGUNSTIGDE_ID>
  <OPMERKING>Remark</OPMERKING>
</PAF_FLOW_EVENT_T>

Anyone any idea. As said tried several approaches, but or my mind is to fuzzy right now or...

Also tried stuff like: http://fgeorges.blogspot.com/2006/08/add-namespace-node-to-element-in.html

...but it seems that for this latest version this could be a bug

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
         declare function local:add-ns-node
  3    4                                  ($elem   as element(),
                                 $prefix as xs:string,
  5    6                                   $ns-uri as xs:string
                                ) as element()
  7    8           {element { QName($ns-uri, concat($prefix, ":x")) }{ $elem }/*}; (: :)
         local:add-ns-node(<xxx><a/></xxx>, "p1", "uri2")
  9   10          '
 11          returning content)
 12          as "XMLQuery Output"
 13  from  dual;

XMLQuery Output
--------------------------------------------------------------------------------
<xxx><a/></xxx>

1 row selected.

Edited by: Marco Gralike on Sep 3, 2010 2:44 PM