This discussion is archived
2 Replies Latest reply: Dec 13, 2012 2:25 AM by Stanislav RSS

Generate XML from object. Bug?

Stanislav Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks a lot!

Legend

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