12 Replies Latest reply: Nov 16, 2010 4:14 PM by Marco Gralike RSS

    Add Namespaces via XQuery to an XML Instance

    Marco Gralike
      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
        • 1. Re: Add Namespaces via XQuery to an XML Instance
          Jason_(A_Non)
          Marco,
          I make no claims about knowing XQuery (yet) so there probably is a better (more correct) way to put this together but here are two ways I came up with to reproduce your desired results. The first is a simple hack assuming you know what you want the root node to look like. The second is a bit more dynamic but I suspect won't work if you have grandchildren in the XML. (This is all on 11.1.0.6)
          SQL> set linesize 120
          SQL> select xmlquery
            2         ('xquery version "1.0"; (: :)
            3           let $x := $i/*/*
            4           return <PAF_FLOW_EVENT_T xmlns="http://www.domainname.nl/pas/events/">{$x}</PAF_FLOW_EVENT_T>
            5           '
            6           passing xmltype(paf) as "i"
            7           returning content
            8          )
            9          as "XMLQuery Output"
           10    from dual;
          
          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</OPMERKI
          NG></PAF_FLOW_EVENT_T>
          
          
          SQL> select xmlquery
            2         ('xquery version "1.0"; (: :)
            3           let $rn := fn:name($i/*)
            4           return element {$rn}
            5                  {attribute xmlns { "http://www.domainname.nl/pas/events/" },
            6                   for $x in $i/*/*
            7                   return element {name($x)} {$x/text()}
            8                  }
            9          '
           10          passing xmltype(paf) as "i"
           11          returning content
           12          )
           13          as "XMLQuery Output"
           14    from dual;
          
          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</OPMERKI
          NG></PAF_FLOW_EVENT_T>
          I ran your local:add-ns-node example through XML Spy and it produced the output shown on that web page. Maybe a bug or not supported feature in 11g yet?

          I ran both of my samples above through Spy as well. The first ran cleanly and the second produced an error message of
          XQuery Execution Error!
          Name expression of a computed attribute constructor returns a QName that is in the namespace http://www.w3.org/TR/REC-xml-names at xmlns
          The second error message makes sense but I haven't validated it. I don't currently have any other engines to throw XQuery through given my experience with Spy's engines.
          • 2. Re: Add Namespaces via XQuery to an XML Instance
            odie_63
            Marco,

            This version of the function should work in 11.2 :
            SELECT XMLQuery(
             'declare function local:add-ns-node
              (
               $elem   as element(),
               $prefix as xs:string,
               $ns-uri as xs:string
              ) 
              as element()
              { 
               element { QName( $ns-uri, 
                                concat( $prefix, 
                                        if ($prefix) then ":" else "",
                                        local-name($elem) ) ) }
               {
                $elem/*
               }
              }; (: :)
              local:add-ns-node($d/*, "", "http://www.domainname.nl/pas/events/")'
             passing xmltype(paf) as "d"
             returning content
            )
            AS "XMLQuery Output"
            FROM dual;
            But personally I'd go for an XSL transformation.
            For example, using a modified version of the identity transformation :
            SQL> SELECT XMLSerialize(DOCUMENT
              2   XMLTransform(
              3    xmltype(paf),
              4    xmltype(
              5    '<xsl:stylesheet  version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
              6      <xsl:output method="xml" omit-xml-declaration="yes"/>
              7      <xsl:param name="ns"/>
              8      <xsl:template match="*">
              9       <xsl:element name="{local-name()}" namespace="{$ns}">
             10        <xsl:apply-templates select="@*|node()"/>
             11       </xsl:element>
             12      </xsl:template>
             13     </xsl:stylesheet>'
             14    ),
             15    q'{ns="'http://www.domainname.nl/pas/events/'"}'
             16   )
             17  AS CLOB INDENT
             18  ) AS "XSLT Output"
             19  FROM dual;
             
            XSLT 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>06/09/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>
             
            Hope that helps.
            • 3. Re: Add Namespaces via XQuery to an XML Instance
              Marco Gralike
              Thanks guys...!!!

              My colleague was strugling with the $i/*/* kind of solutions (we didn't like it...), so I had in XQuery the same solution as A_Non but, as said, I didn't like the $i/*/* on the other hand I couldn't get the QName solution to work and me not being the best on XQuery topics couldn't find a different solution...

              The following was what my colleague had come up with, with the '/*/*' solution in it
              select XMLElement ("dummy", XMLAttributes('http://www.domainname.nl/pas/events/' as "xmlns"), 
                                 XMLForest(p_event as "PAS_FLOW_EVENT"))
              into l_dummy
              from dual;
              
              l_resultXML := l_dummy.extract('/*/*');
              so the one query solution would have been
              select XMLElement ("dummy", XMLAttributes('http://www.domainname.nl/pas/events/' as "xmlns"), 
                                 XMLForest(paf as "PAS_FLOW_EVENT_T")).extract('/*/*') as "Namespace Okay"
              from   dual;
              M.

              Edited by: Marco Gralike on Nov 16, 2010 12:13 PM
              • 4. Re: Add Namespaces via XQuery to an XML Instance
                Marco Gralike
                Sorry "Odie" wanted to mark your solution as "more" correct, but apparently I am not allowed to do that via this web app...
                • 5. Re: Add Namespaces via XQuery to an XML Instance
                  Jason_(A_Non)
                  I always like read your answers odie_63 as I usually end up learning something new too.

                  Where did you find information regarding the third parm for XMLTransform as I don't see it in the on-line documentation for XMLTransform under the 11.1 nor 11.2 documentation. I've played around with it and found several things that don't work, including an ORA-0600 (probably) that killed my session to 11.1.0.6.
                  • 6. Re: Add Namespaces via XQuery to an XML Instance
                    odie_63
                    I always like read your answers
                    Thanks.
                    Where did you find information regarding the third parm for XMLTransform as I don't see it in the on-line documentation for XMLTransform under the 11.1 nor 11.2 documentation.
                    It's funny, now you mention it, I don't even remember.

                    I've probably got inspired by the XMLType.transform method which has that functionality documented.
                    • 7. Re: Add Namespaces via XQuery to an XML Instance
                      Marco Gralike
                      Thats funny, I thought the same and looked it up with transform, but couldn't find it. Could you place an URL here were it is mentioned?
                      • 8. Re: Add Namespaces via XQuery to an XML Instance
                        odie_63
                        Could you place an URL here were it is mentioned?
                        Yes, it's in Packages and Types Reference :
                        http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/t_xml.htm#i1009783
                        • 9. Re: Add Namespaces via XQuery to an XML Instance
                          Marco Gralike
                          Re-reading this, I think I hadn't my day. I should have know transform (xmltype...). Stupid.
                          • 10. Re: Add Namespaces via XQuery to an XML Instance
                            Marco Gralike
                            This is "funny".

                            I tried to find an alternative, based on some of your insertXMLChild, examples given lately...
                            http://download.oracle.com/docs/cd/E14072_01/appdev.112/e10492/xdb04cre.htm#CHDIIACJ

                            I tried multiple options of course but the nearest thing that almost fits, does to much (= extra gives xmlns=""

                            SQL> select insertChildXML((XMLForest(paf as "PAS_FLOW_EVENT_T"))
                              2                  , '/PAS_FLOW_EVENT_T'
                              3                  , '@n'
                              4                  , 'http://www.domainname.nl/pas/events/'
                              5                  ) as "Namespace Okay?"
                              6  from dual;
                            
                            Namespace Okay?
                            --------------------------------------------------------------------------------
                            <PAS_FLOW_EVENT_T n="http://www.domainname.nl/pas/events/"><PROCES_ID>1</PROCE
                            S_ID><PROCES_TYPE>ProcessType</PROCES_TYPE><PROCES_NAME>ProcessName</PROCES_NAME>
                            <PROCES_AANMAAKDATUM>16-NOV-10</PROCES_AANMAAKDATUM><EVENT>EventName</EVENT><OS
                            _USER>OSUser</OS_USER><BETROKKENE_ID>999</BETROKKENE_ID><BEGUNSTIGDE_ID>666</BEG
                            UNSTIGDE_ID><OPMERKING>Remark</OPMERKING></PAS_FLOW_EVENT_T>
                            
                            
                            1 row selected.
                            So based on this you would expect..., but it results in...(double xmlns declaration)
                            SQL> select insertChildXML((XMLForest(paf as "PAS_FLOW_EVENT_T"))
                              2                  , '/PAS_FLOW_EVENT_T'
                              3                  , '@xmlns'
                              4                  , 'http://www.domainname.nl/pas/events/'
                              5                  ) as "Namespace Okay?"
                              6  from dual;
                            
                            Namespace Okay?
                            --------------------------------------------------------------------------------
                            <PAS_FLOW_EVENT_T xmlns="" xmlns="http://www.domainname.nl/pas/events/"><PROCES_
                            ID>1</PROCES_ID><PROCES_TYPE>ProcessType</PROCES_TYPE><PROCES_NAME>ProcessName</
                            PROCES_NAME><PROCES_AANMAAKDATUM>16-NOV-10</PROCES_AANMAAKDATUM><EVENT>EventName
                            </EVENT><OS_USER>OSUser</OS_USER><BETROKKENE_ID>999</BETROKKENE_ID><BEGUNSTIGDE_
                            ID>666</BEGUNSTIGDE_ID><OPMERKING>Remark</OPMERKING></PAS_FLOW_EVENT_T>
                            
                            
                            1 row selected.
                            What do you think? A new "feature" SR worthy?

                            Edited by: Marco Gralike on Nov 16, 2010 1:23 PM
                            • 11. Re: Add Namespaces via XQuery to an XML Instance
                              odie_63
                              Hi Marco,
                              A new "feature" SR worthy?
                              Possibly.

                              It seems related to XMLForest usage though, because it does work with XMLType and SYS_XMLGen constructors (version 11.2.0.1) :
                              SQL> select xmlserialize(document
                              insertChildXML(
                              sys_xmlgen(paf, xmlformat('PAF_FLOW_EVENT_T'))
                              , '/PAF_FLOW_EVENT_T'
                              , '@xmlns'
                              , 'http://www.domainname.nl/pas/events/'
                              ) as clob indent) result
                              from dual;
                               
                              RESULT
                              --------------------------------------------------------------------------------
                              <?xml version="1.0"?>
                              <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>16/11/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>
                               
                              SQL> select xmlserialize(document
                              insertChildXML(
                              xmltype(paf)
                              , '/PAF_FLOW_EVENT_T'
                              , '@xmlns'
                              , 'http://www.domainname.nl/pas/events/'
                              ) as clob indent) result
                              from dual;
                               
                              RESULT
                              --------------------------------------------------------------------------------
                              <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>16/11/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>
                               
                              • 12. Re: Add Namespaces via XQuery to an XML Instance
                                Marco Gralike
                                It seems related to XMLForest usage though, because it does work with XMLType and SYS_XMLGen constructors (version 11.2.0.1) :
                                Hmmm, wasn't aware (yet), thanks.