2 Replies Latest reply: Dec 13, 2012 4:25 AM by Stanislav RSS

    Generate XML from object. Bug?

    Stanislav
      Hi, All!

      I want generate XML with using type objects and builtin function XMLTYPE.CreateXML ()

      Create some types for example
      create or replace type TEST_FIELDS_TYPE is object
      (
          INT_NODE INTEGER,
          STRING_NODE NVARCHAR2 (200),
          DATE_NODE DATE
      ); 
      /
      create or replace type TEST_MY_OBJECTS_LIST_TYPE is TABLE OF TEST_FIELDS_TYPE; 
      /
      create or replace type TEST_MY_SUBNODES IS OBJECT (SUB_NODE TEST_MY_OBJECTS_LIST_TYPE);
      /
      create or replace type TEST_MY_ROOT_TYPE is object
      (
        SUB_ROOT_NODE1 VARCHAR(100),
        SUB_ROOT_NODE2 VARCHAR(100),
        SUB_ROOT_NODE3 VARCHAR(100),
        SUB_NODES TEST_MY_SUBNODES  
      );
      /
       
      Generate XSD schema
      select dbms_xmlschema.generateschema (USER,'TEST_MY_ROOT_TYPE') from dual
      Generated schema
      <?xml version="1.0"?>
      <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xdb="http://xmlns.oracle.com/xdb" xsi:schemaLocation="http://xmlns.oracle.com/xdb http://xmlns.oracle.com/xdb/XDBSchema.xsd">
        <xsd:element name="TEST_MY_ROOT_TYPE" type="TEST_MY_ROOT_TYPEType" xdb:SQLType="TEST_MY_ROOT_TYPE" xdb:SQLSchema="***Your Schema***"/>
        <xsd:complexType name="TEST_MY_ROOT_TYPEType" xdb:SQLType="TEST_MY_ROOT_TYPE" xdb:SQLSchema="***Your Schema***" xdb:maintainDOM="false">
          <xsd:sequence>
            <xsd:element name="SUB_ROOT_NODE1" xdb:SQLName="SUB_ROOT_NODE1" xdb:SQLType="VARCHAR2">
              <xsd:simpleType>
                <xsd:restriction base="xsd:string">
                  <xsd:maxLength value="100"/>
                </xsd:restriction>
              </xsd:simpleType>
            </xsd:element>
            <xsd:element name="SUB_ROOT_NODE2" xdb:SQLName="SUB_ROOT_NODE2" xdb:SQLType="VARCHAR2">
              <xsd:simpleType>
                <xsd:restriction base="xsd:string">
                  <xsd:maxLength value="100"/>
                </xsd:restriction>
              </xsd:simpleType>
            </xsd:element>
            <xsd:element name="SUB_ROOT_NODE3" xdb:SQLName="SUB_ROOT_NODE3" xdb:SQLType="VARCHAR2">
              <xsd:simpleType>
                <xsd:restriction base="xsd:string">
                  <xsd:maxLength value="100"/>
                </xsd:restriction>
              </xsd:simpleType>
            </xsd:element>
            <xsd:element name="SUB_NODES" type="TEST_MY_SUBNODESType" xdb:SQLName="SUB_NODES" xdb:SQLSchema="***Your Schema***" xdb:SQLType="TEST_MY_SUBNODES"/>
          </xsd:sequence>
        </xsd:complexType>
        <xsd:complexType name="TEST_MY_SUBNODESType" xdb:SQLType="TEST_MY_SUBNODES" xdb:SQLSchema="***Your Schema***" xdb:maintainDOM="false">
          <xsd:sequence>
            <xsd:element name="SUB_NODE" type="TEST_FIELDS_TYPEType" maxOccurs="unbounded" minOccurs="0" xdb:SQLName="SUB_NODE" xdb:SQLCollType="TEST_MY_OBJECTS_LIST_TYPE" xdb:SQLType="TEST_FIELDS_TYPE" xdb:SQLSchema="***Your Schema***" xdb:SQLCollSchema="***Your Schema***"/>
          </xsd:sequence>
        </xsd:complexType>
        <xsd:complexType name="TEST_FIELDS_TYPEType" xdb:SQLType="TEST_FIELDS_TYPE" xdb:SQLSchema="***Your Schema***" xdb:maintainDOM="false">
          <xsd:sequence>
            <xsd:element name="INT_NODE" type="xsd:integer" xdb:SQLName="INT_NODE" xdb:SQLType="INTEGER"/>
            <xsd:element name="STRING_NODE" xdb:SQLName="STRING_NODE" xdb:SQLType="VARCHAR2">
              <xsd:simpleType>
                <xsd:restriction base="xsd:string">
                  <xsd:maxLength value="400"/>
                </xsd:restriction>
              </xsd:simpleType>
            </xsd:element>
            <xsd:element name="DATE_NODE" type="xsd:date" xdb:SQLName="DATE_NODE" xdb:SQLType="DATE"/>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:schema>
      Now i want get data with using TEST_MY_ROOT_TYPE type
      Create example function for init object and generate XML
      create or replace function test_xmltype_function return XMLTYPE
      is
        xml_res TEST_MY_ROOT_TYPE;
        subnodes TEST_MY_OBJECTS_LIST_TYPE;
      begin
        xml_res := EAS.TEST_MY_ROOT_TYPE('node1','node2','node3',TEST_MY_SUBNODES(TEST_MY_OBJECTS_LIST_TYPE()));
        subnodes := TEST_MY_OBJECTS_LIST_TYPE();
        subnodes.extend;
        subnodes(1):= TEST_FIELDS_TYPE(null,null,null);
        subnodes(1).INT_NODE :=1;
        subnodes(1).STRING_NODE :='This is string 1';
        subnodes(1).DATE_NODE :=sysdate();
        subnodes.extend;
        subnodes(2):= TEST_FIELDS_TYPE(null,null,null);
        subnodes(2).INT_NODE :=2;
        subnodes(2).STRING_NODE :='This is string 2';
        subnodes(2).DATE_NODE :=sysdate();   
        xml_res.SUB_NODES.SUB_NODE := subnodes;
        return XMLTYPE.createxml (xml_res); 
      end;
      And now try get some xml and call this function
      select test_xmltype_function from dual;
      See on returned XML, they is wrong! and not valid for generated XSD schema
      <TEST_MY_ROOT_TYPE>
        <SUB_ROOT_NODE1>node1</SUB_ROOT_NODE1>
        <SUB_ROOT_NODE2>node2</SUB_ROOT_NODE2>
        <SUB_ROOT_NODE3>node3</SUB_ROOT_NODE3>
        <SUB_NODES>
          <SUB_NODE>
            <TEST_FIELDS_TYPE>
              <INT_NODE>1</INT_NODE>
              <STRING_NODE>This is string 1</STRING_NODE>
              <DATE_NODE>13.12.12</DATE_NODE>
            </TEST_FIELDS_TYPE>
            <TEST_FIELDS_TYPE>
              <INT_NODE>2</INT_NODE>
              <STRING_NODE>This is string 2</STRING_NODE>
              <DATE_NODE>13.12.12</DATE_NODE>
            </TEST_FIELDS_TYPE>
          </SUB_NODE>
        </SUB_NODES>
      </TEST_MY_ROOT_TYPE>
      This is valide XML for generated XDS schema
      <TEST_MY_ROOT_TYPE xsi:noNamespaceSchemaLocation="datatype.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
           <SUB_ROOT_NODE1>some text 1<SUB_ROOT_NODE1>
           <SUB_ROOT_NODE2>some text 2<SUB_ROOT_NODE2>
           <SUB_ROOT_NODE3>some text 3<SUB_ROOT_NODE3>
           <SUB_NODES>
                <SUB_NODE>
                     <INT_NODE>2</INT_NODE>
                     <STRING_NODE>This is string 2</STRING_NODE>
                     <DATE_NODE>2013-12-12</DATE_NODE>
                </SUB_NODE>
                <SUB_NODE>
                     <INT_NODE>2</INT_NODE>
                     <STRING_NODE>This is string 2</STRING_NODE>
                     <DATE_NODE>2013-12-12</DATE_NODE>
                </SUB_NODE>
                <SUB_NODE>
                     <INT_NODE>2</INT_NODE>
                     <STRING_NODE>This is string 2</STRING_NODE>
                     <DATE_NODE>2013-12-12</DATE_NODE>
                </SUB_NODE>          
           </SUB_NODES>
      </TEST_MY_ROOT_TYPE>
      Why data from node <SUB_NODE> included in child nodes <TEST_FIELDS_TYPE>, in XSD schema - node <SUB_NODE> must have only 3 child nodes INT_NODE, STRING_NODE, DATE_NODE
      and generated XML not valid for genenarated schema. Why???

      Please help! where i have error?
        • 1. Re: Generate XML from object. Bug?
          odie_63
          Hi,

          Thanks for this very clear test case.
          Why data from node <SUB_NODE> included in child nodes <TEST_FIELDS_TYPE>, in XSD schema - node <SUB_NODE> must have only 3 child nodes INT_NODE, STRING_NODE, DATE_NODE
          and generated XML not valid for genenarated schema. Why???
          Actually, there's a mismatch between the generated schema using DBMS_XMLSCHEMA and the format returned by the constructor.
          That's because without additional parameters the XMLType constructor uses a default canonical Object to XML mapping to build an XML instance out of an ADT instance.

          In order to create an XML instance document that conforms to the generated XSD, you must register the XSD :
          begin
             dbms_xmlschema.registerSchema(
               schemaURL => 'my_schema.xsd'
             , schemaDoc => dbms_xmlschema.generateschema (USER,'TEST_MY_ROOT_TYPE')
             , local => true
             , genTypes => false
             , genTables => false
             , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE 
             );
          end;
          /
          Then in the function's RETURN clause, use the schema-aware XMLType constructor :
          return xmltype(xml_res, 'my_schema.xsd', 'TEST_MY_ROOT_TYPE');
          test :
          SQL> select xmlserialize(document test_xmltype_function) from dual;
           
          XMLSERIALIZE(DOCUMENTTEST_XMLT
          --------------------------------------------------------------------------------
          <TEST_MY_ROOT_TYPE>
            <SUB_ROOT_NODE1>node1</SUB_ROOT_NODE1>
            <SUB_ROOT_NODE2>node2</SUB_ROOT_NODE2>
            <SUB_ROOT_NODE3>node3</SUB_ROOT_NODE3>
            <SUB_NODES>
              <SUB_NODE>
                <INT_NODE>1</INT_NODE>
                <STRING_NODE>This is string 1</STRING_NODE>
                <DATE_NODE>2012-12-13</DATE_NODE>
              </SUB_NODE>
              <SUB_NODE>
                <INT_NODE>2</INT_NODE>
                <STRING_NODE>This is string 2</STRING_NODE>
                <DATE_NODE>2012-12-13</DATE_NODE>
              </SUB_NODE>
            </SUB_NODES>
          </TEST_MY_ROOT_TYPE>
           
          Note : for this to work I also had to use VARCHAR2 instead of NVARCHAR2 (STRING_NODE attribute).
          National charset columns are not well supported within XML operations.
          • 2. Re: Generate XML from object. Bug?
            Stanislav
            Thanks a lot!