5 Replies Latest reply: Jul 3, 2013 7:06 AM by Pollocks01 RSS

    How to add namespace prefixes to XMLType created from Object?

    Pollocks01
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE 11.2.0.3.0 Production
      TNS for Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      

       

       

      I'm working with SOAP request creation from Schema derived Types.

       

      Consider the registration of the following annotated schema (I wanted Oracle to create the types for me, but with my own names):

       

       

      exec dbms_xmlschema.deleteSchema(schemaURL => 'Parameters4.xsd');
      
      declare
      v_xsd xmltype := xmltype('<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
                 xmlns:oraxdb="http://xmlns.oracle.com/xdb"
                 xmlns = "http://www.cognera.com"
                 targetNamespace = "http://www.cognera.com">
        <xs:element name="Parameters" oraxdb:SQLName="Parameters" oraxdb:SQLType="Parameters">
          <xs:complexType oraxdb:SQLType="Parameters">
            <xs:sequence>
              <xs:element name="Param1" type="xs:string" oraxdb:SQLName="Param1" oraxdb:SQLType="VARCHAR2" />
              <xs:element name="NestedItems" oraxdb:SQLName="NestedItems" oraxdb:SQLType="NestedItemsType">
                <xs:complexType oraxdb:SQLType="NestedItemsType">
                  <xs:sequence>
                    <xs:element name="NestedItem" type="NestedItemType" oraxdb:SQLName="NestedItem" oraxdb:SQLType="NestedItemType"/>
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:complexType name="NestedItemType" oraxdb:SQLType="NestedItemType">
          <xs:sequence>
            <xs:element name="nParam1" type="xs:string" oraxdb:SQLName="nParam1" oraxdb:SQLType="VARCHAR2"/>
            <xs:element name="nParam2" type="xs:string" oraxdb:SQLName="nParam2" oraxdb:SQLType="VARCHAR2"/>
            <xs:element name="nParam3" type="xs:string" oraxdb:SQLName="nParam3" oraxdb:SQLType="VARCHAR2"/>
          </xs:sequence>
        </xs:complexType>
      </xs:schema>
      ');
      begin
        dbms_xmlschema.registerSchema(schemaURL => 'Parameters4.xsd', --this name is local to each ERS schema.                                 
                                            schemaDoc => v_xsd,
                                            local => TRUE,
                                            genTypes => TRUE, --only want the types
                                            genbean => FALSE,
                                            genTables => TRUE, --not sure if I need this
                                            force => TRUE,
                                            owner => user);
      end;
      /
      

       

       

      Types created were:

       

       

      CREATE OR REPLACE TYPE "NestedItemType" AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T","nParam1" VARCHAR2(4000 CHAR),"nParam2" VARCHAR2(4000 CHAR),"nParam3" VARCHAR2(4000 CHAR))NOT FINAL INSTANTIABLE
      /
      CREATE OR REPLACE TYPE "NestedItemsType" AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T","NestedItem" "NestedItemType")FINAL INSTANTIABLE
      /
      CREATE OR REPLACE TYPE "Parameters" AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T","Param1" VARCHAR2(4000 CHAR),"NestedItems" "NestedItemsType")FINAL INSTANTIABLE
      /
      

       

      I found that in order to build these types using constructors (to avoid PLS-00306: wrong number or types of arguments in call to 'NestedItemType'), that I needed to edit these types (drop the xdb magic):

       

      CREATE OR REPLACE TYPE "NestedItemType" AS OBJECT ("nParam1" VARCHAR2(4000 CHAR),"nParam2" VARCHAR2(4000 CHAR),"nParam3" VARCHAR2(4000 CHAR))FINAL INSTANTIABLE
      /
      CREATE OR REPLACE TYPE "NestedItemsType" AS OBJECT ("NestedItem" "NestedItemType")FINAL INSTANTIABLE
      /
      CREATE OR REPLACE TYPE "Parameters" AS OBJECT ("Param1" VARCHAR2(4000 CHAR),"NestedItems" "NestedItemsType")FINAL INSTANTIABLE
      /
      

       

       

      I read on the forums of a hack to get a namespace added in the output:

       

       

      CREATE OR REPLACE TYPE "Parameters" AS OBJECT ("@xmlns" VARCHAR2(4000), -- namespace attribute HACK
                                                             "Param1" VARCHAR2(4000 CHAR),"NestedItems" "NestedItemsType")FINAL INSTANTIABLE
      /
      

       

       

      Putting it all together, I have:

       

      DECLARE
        v_Parameters    "Parameters";
        v_xml           xmltype;
        v_print_output  clob;      
      begin
        v_Parameters :=  "Parameters"('www.cognera.com',
                                      'hello',
                                    "NestedItemsType"("NestedItemType"('one',
                                                                   'two',
                                                                   'three'
                                                                  )
                                                   )
                                   );
          
        v_xml := xmltype(xmlData => v_Parameters);
      
        select xmlserialize(document
                            xmlquery('declare namespace soap = "http://www.w3.org/2003/05/soap-envelope";                                                              
                                      declare namespace cognera = "http://www.cognera.com";
                                      <soap:Envelope>
                                         <soap:Header/>
                                         <soap:Body>
                                           {/}
                                         </soap:Body>
                                       </soap:Envelope>
                                     ' passing v_xml returning content) as clob
                            indent size=2
                           ) into v_print_output from dual;
        dbms_output.put_line(v_print_output);
      end;
      

       

      This outputs:

       

      <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope">
        <soap:Header/>
        <soap:Body>
          <Parameters xmlns="www.cognera.com">
            <Param1>hello</Param1>
            <NestedItems>
              <NestedItem>
                <nParam1>one</nParam1>
                <nParam2>two</nParam2>
                <nParam3>three</nParam3>
              </NestedItem>
            </NestedItems>
          </Parameters>
        </soap:Body>
      </soap:Envelope>
      

       

      What I really want is:

       

      <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:c="www.cognera.com">
        <soap:Header/>
        <soap:Body>
          <c:Parameters>
            <c:Param1>hello</c:Param1>
            <c:NestedItems>
              <c:NestedItem>
                <c:nParam1>one</c:nParam1>
                <c:nParam2>two</c:nParam2>
                <c:nParam3>three</c:nParam3>
              </c:NestedItem>
            </c:NestedItems>
          </c:Parameters>
        </soap:Body>
      </soap:Envelope>
      

       

       

      How do I do this without having to programatically add the namespace using a custom XQuery function?

        • 1. Re: How to add namespace prefixes to XMLType created from Object?
          odie_63

          The two outputs are semantically equivalent.

          Why do you need the second one?

          • 2. Re: How to add namespace prefixes to XMLType created from Object?
            Pollocks01

            Odie - if they are truly the same, maybe the first will be OK. This if for integration with BI Publisher so I suppose I can try the first output to see if it works fine.

             

            I came up with the following, which is a hack but gets one step closer to what I initially wanted:

             

            DROP TYPE "c:Parameters";
            DROP TYPE "NestedItemsType";
            DROP TYPE "NestedItemType";
            
            CREATE OR REPLACE TYPE "NestedItemType" AS OBJECT ("c:nParam1" VARCHAR2(4000 CHAR),
                                                               "c:nParam2" VARCHAR2(4000 CHAR),
                                                               "c:Param3" VARCHAR2(4000 CHAR))FINAL INSTANTIABLE
            /
            
            
            CREATE OR REPLACE TYPE "NestedItemsType" AS OBJECT ("c:NestedItem" "NestedItemType")FINAL INSTANTIABLE
            /
            
            
            CREATE OR REPLACE TYPE "c:Parameters" AS OBJECT ("@xmlns:c" VARCHAR2(4000 CHAR),
                                                             "c:Param1" VARCHAR2(4000 CHAR),
                                                             "c:NestedItems" "NestedItemsType")FINAL INSTANTIABLE
            /
            
            
            DECLARE
              v_Parameters    "c:Parameters";
              v_xml           xmltype;
              v_print_output  clob;      
            begin
              v_Parameters :=  "c:Parameters"('www.cognera.com',
                                              'hello',
                                              "NestedItemsType"("NestedItemType"('one',
                                                                                 'two',
                                                                                 'three'
                                                                                )
                                                               )
                                              );
                
              v_xml := xmltype(xmlData => v_Parameters);
            
            
              select xmlserialize(document
                                  xmlquery('declare namespace soap = "http://www.w3.org/2003/05/soap-envelope";                                                             
                                            declare namespace c = "http://www.cognera.com";
                                            <soap:Envelope xmlns:c="www.cognera.com">
                                               <soap:Header/>
                                               <soap:Body>
                                                 {/}
                                               </soap:Body>
                                             </soap:Envelope>
                                           ' passing v_xml returning content) as clob
                                  indent size=2
                                 ) into v_print_output from dual;
              dbms_output.put_line(v_print_output);
            end;
            

             

            <soap:Envelope xmlns:c="www.cognera.com" xmlns:soap="http://www.w3.org/2003/05/soap-envelope">
              <soap:Header/>
              <soap:Body>
                <c:Parameters xmlns:c="www.cognera.com">
                  <c:Param1>hello</c:Param1>
                  <c:NestedItems>
                    <c:NestedItem>
                      <c:nParam1>one</c:nParam1>
                      <c:nParam2>two</c:nParam2>
                      <c:Param3>three</c:Param3>
                    </c:NestedItem>
                  </c:NestedItems>
                </c:Parameters>
              </soap:Body>
            </soap:Envelope>
            
            • 3. Re: How to add namespace prefixes to XMLType created from Object?
              Pollocks01

              Odie - I'll give you the points for this because I'm not going to be able to verify anytime soon, but I do understand what you're saying and think you're right!

               

              Thanks!

              • 4. Re: How to add namespace prefixes to XMLType created from Object?
                odie_63

                See this similar thread, it should give you some better alternatives than the "@xmlns" hack :

                 

                Add Namespaces via XQuery to an XML Instance

                 

                For example :

                SQL> SELECT XMLSerialize(DOCUMENT

                  2           XMLTransform(

                  3             xmltype(

                  4               "Parameters"('hello', "NestedItemsType"("NestedItemType"('one','two','three')))

                  5             )

                  6           , xmlparse(content

                  7  '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

                  8    <xsl:output encoding="UTF-8" indent="yes" method="xml"/>

                  9    <xsl:param name="ns"/>

                10    <xsl:template match="/">

                11      <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope">

                12        <soap:Header/>

                13          <soap:Body>

                14               <xsl:apply-templates select="*"/>

                15             </soap:Body>

                16         </soap:Envelope>

                17    </xsl:template>

                18    <xsl:template match="*">

                19      <xsl:element name="{local-name()}" namespace="{$ns}">

                20        <xsl:apply-templates select="@*|node()"/>

                21      </xsl:element>

                22    </xsl:template>

                23  </xsl:stylesheet>')

                24           , q'{ns="'www.cognera.com'"}'

                25           )

                26           INDENT

                27         ) AS "XSLT Output"

                28  FROM dual ;

                 

                XSLT Output

                --------------------------------------------------------------------------------

                <?xml version="1.0" encoding="UTF-8"?>

                <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope">

                  <soap:Header/>

                  <soap:Body>

                    <Parameters xmlns="www.cognera.com">

                      <Param1>hello</Param1>

                      <NestedItems>

                        <NestedItem>

                          <nParam1>one</nParam1>

                          <nParam2>two</nParam2>

                          <nParam3>three</nParam3>

                        </NestedItem>

                      </NestedItems>

                    </Parameters>

                  </soap:Body>

                </soap:Envelope>

                 

                You can store the stylesheet in the XDB repository or in a table and access it using a DBUriType.

                It then provides a concise way to both add the namespace and wrap the payload in the envelope.

                • 5. Re: How to add namespace prefixes to XMLType created from Object?
                  Pollocks01

                  Thanks Odie - yes, the XSLT is much nicer. I hadn't thought about that way to wrap the request body in a soap envelope. I did consider using a custom XQuery function to walk the XML, adding the namespace "properly", but didn't like that solution as I thought that we should have been able to do this using native Oracle functionality (using the toobject method).

                   

                  I actually played with an alternative approach here too:

                   

                  with xml_data as
                  (select xmltype('<rowset>
                                    <row>
                                      <col1>?</col1>
                                      <col2>?</col2>
                                    </row>
                                  </rowset>') as xml_data,
                          'column 1 value' as col1,
                          'column 2 value' as col2
                  from dual)
                  select xmlquery('copy $doc := .
                                   modify (
                                            for $row in $doc/rowset/row return (
                                                 replace value of node $row/col1 with $col1,
                                                 replace value of node $row/col2 with $col2
                                                                               )                                      
                                          )
                                   return $doc'
                                   passing xml_data, col1 as "col1", col2 as "col2"
                                   returning content) from xml_data;
                  
                  

                   

                  produces:

                   

                  <rowset>
                    <row>
                      <col1>column 1 value</col1>
                      <col2>column 2 value</col2>
                    </row>
                  </rowset>
                  
                  

                   

                  ...of course if the 'template' xml contains namespace info and the xmlquery is made to be namespace aware, then this approach should work. This solution is inspired by what SOAPUI gives you when you feed it a WSDL - it instantiates the schema for you, giving you a skeletal XML with question marks (?) as placeholders.

                   

                  AND, this approach lets us play with the 11.2.0.3.0 brand new feature - XML Update. This removes the complexity of having to manage those SQL types, schema annotations etc.

                   

                  Thanks - very educational!

                   

                  -P

                   

                  Message was edited by: Pollocks01