This discussion is archived
5 Replies Latest reply: Jul 3, 2013 5:06 AM by Pollocks01 RSS

How to add namespace prefixes to XMLType created from Object?

Pollocks01 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points