14 Replies Latest reply: Mar 3, 2013 11:20 AM by 794743 RSS

    How to create default column in XML schema ?

    794743
      Hi All,

      I would like to define a default column let's say Run_Date as Date datatype in XML schema definition (XSD) and would like to populate SYSDATE to the column Run_Date, When XML records are parsed to that schema.
      Please let me know, how we can achieve?

      Thanks in Advance.
        • 1. Re: How to create default column in XML schema ?
          odie_63
          Hi,

          You'll have to explain what you want to do more clearly. Right now, I don't have a clue about what you're after.

          Are you using XML DB features?
          What's the db version?
          What does the schema look like?
          • 2. Re: How to create default column in XML schema ?
            794743
            Hi Odie,

            Sorry.I did not give you the full details. Please find below as required.

            The schema definition is as follows

            <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0" xdb:storeVarrayAsTable="true">
                                                    <xs:element name="Emp" type="EmpType" xdb:defaultTable="EMPLOYEES" xdb:columnProps="CONSTRAINT emp_pk PRIMARY KEY (XMLDATA.EMP_NO)" />
                                                    <xs:complexType name="EmpType" xdb:SQLType="EMPLOYEES_T">
                                                         <xs:sequence>
                                                              <xs:element name="EmpNo" xdb:SQLName="EMP_NO">
                                                                   <xs:simpleType>
                                                                        <xs:restriction base="xs:positiveInteger">
                                                                             <xs:totalDigits value="10"/>
                                                                        </xs:restriction>
                                                                   </xs:simpleType>
                                                              </xs:element>
                                                              <xs:element name="FirstName" xdb:SQLName="FIRST_NAME">
                                                                   <xs:simpleType>
                                                                        <xs:restriction base="xs:string">
                                                                             <xs:maxLength value="30"/>
                                                                        </xs:restriction>
                                                                   </xs:simpleType>
                                                              </xs:element>
                                                              <xs:element name="LastName" xdb:SQLName="LAST_NAME">
                                                                   <xs:simpleType>
                                                                        <xs:restriction base="xs:string">
                                                                             <xs:maxLength value="30"/>
                                                                        </xs:restriction>
                                                                   </xs:simpleType>
                                                              </xs:element>
                                                              <xs:element name="EmployeeType" xdb:SQLName="EMPLOYEE_TYPE">
                                                                   <xs:simpleType>
                                                                        <xs:restriction base="xs:string">
                                                                             <xs:length value="25"/>
                                                                             <xs:enumeration value="Permanent"/>                              
                                                                             <xs:enumeration value="Consultant"/>
                                                                        </xs:restriction>
                                                                   </xs:simpleType>
                                                              </xs:element>
                                                              <xs:element name="EmpLocation" xdb:SQLName="EMP_LOCATION">
                                                                   <xs:simpleType>
                                                                        <xs:restriction base="xs:string">
                                                                             <xs:maxLength value="25"/>
                                                                             <xs:enumeration value="NewYork"/>
                                                                        </xs:restriction>
                                                                   </xs:simpleType>
                                                              </xs:element>
                                                              <xs:element name="EmpExperience" xdb:SQLName="EMP_EXPERIENCE">
                                                                   <xs:simpleType>
                                                                        <xs:restriction base="xs:positiveInteger">
                                                                             <xs:totalDigits value="10"/>
                                                                        </xs:restriction>
                                                                   </xs:simpleType>
                                                              </xs:element>
                                                         </xs:sequence>
                                                    </xs:complexType>
                                          </xs:schema>

            The sample XML file is as follows

            <?xml version="1.0"?>
            <Emp xsi:noNamespaceSchemaLocation="EMP.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xdb="http://xmlns.oracle.com/xdb">
                 <EmpNo>1001</EmpNo>
                 <FirstName>William</FirstName>
                 <LastName>Don</LastName>
                 <EmployeeType>Permanent</EmployeeType>
                 <EmpLocation>NewYork</EmpLocation>
                 <EmpExperience>10</EmpExperience>
            </Emp>

            The Oracle Version is

            Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
            PL/SQL Release 10.2.0.3.0 - Production
            CORE 10.2.0.3.0 Production

            The XML Version is

            Oracle XML Database 10.2.0.3.0

            What I need is, I would like to have a column Run_Date with date as datetype and I would to populate(Something like default value defined in table creation) with Sysdate where XML records are inserted into EMP table.
            Please let me know how to achieve it? Because, XML records are processed and stored in this table, But I could not see at what date they are populated.
            Also, I cannot change XML file content, since it is generated by other language , But I can change XML schema definition if needed.

            Thanks in Advance
            • 3. Re: How to create default column in XML schema ?
              odie_63
              Also, I cannot change XML file content, since it is generated by other language , But I can change XML schema definition if needed.
              Well, if you cannot change the XML instance, then you cannot change the schema it conforms to.
              You could add an optional attribute with default value but we can only use a static value.

              How are you inserting the file into the table?

              <li>If you're doing it programatically, via an INSERT statement, then I think your best bet is to create a new table with a relational schema-based XMLType column and a DATE column :
              CREATE TABLE employees (
                dt_created date default sysdate not null 
              , xmldoc     xmltype
              , CONSTRAINT emp_pk PRIMARY KEY (xmldoc."XMLDATA".EMP_NO)
              )
              XMLTYPE COLUMN xmldoc STORE AS OBJECT RELATIONAL
              XMLSCHEMA "EMP.xsd" ELEMENT "Emp" ;
              If you choose to do so, delete the XML schema and register it again with "genTables => false".


              <li>If you're inserting the file via the XDB repository (FTP, WebDAV, createResource) and relying on the default table feature, then you may be out of luck.
              There's probably a way to tamper with the underlying OR structure to make one of a hidden column accept a default value, but since it's not supported I won't even try.
              • 4. Re: How to create default column in XML schema ?
                794743
                Hi Odie,

                Thanks a lot. Let me try this approach.

                Regards,
                • 5. Re: How to create default column in XML schema ?
                  794743
                  Hi,

                  I am reopening this thread, Since I am facing little issue with the current subject. I went by the approach what you suggested and When I tried to do CopyEvolve, It worked perfectly fine.
                  When I tried to move back the data from temp table(Created during CopyEvolve) to main table,Not all the data are moved back to main table. Meaning, nested element's data are not getting copied.

                  Can you please suggest, What could be the reason?

                  Thanks in Advance.
                  • 6. Re: How to create default column in XML schema ?
                    odie_63
                    What did you change in the schema?

                    Please post all steps of the CopyEvolve process.
                    • 7. Re: How to create default column in XML schema ?
                      794743
                      Hi,

                      Thanks for the update. Please find below my responses and let me know your thoughts.

                      The Existing schema definition is as follows

                      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0" xdb:storeVarrayAsTable="true">
                      <xs:element name="Emp" type="EmpType" xdb:defaultTable="EMPLOYEES" xdb:columnProps="CONSTRAINT emp_pk PRIMARY KEY (XMLDATA.EMP_NO)" />
                      <xs:complexType name="EmpType" xdb:SQLType="EMPLOYEES_T">
                      <xs:sequence>
                      <xs:element name="EmpNo" xdb:SQLName="EMP_NO">
                      <xs:simpleType>
                      <xs:restriction base="xs:positiveInteger">
                      <xs:totalDigits value="10"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="FirstName" xdb:SQLName="FIRST_NAME">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:maxLength value="30"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="LastName" xdb:SQLName="LAST_NAME">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:maxLength value="30"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="EmployeeType" xdb:SQLName="EMPLOYEE_TYPE">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:length value="25"/>
                      <xs:enumeration value="Permanent"/>     
                      <xs:enumeration value="Consultant"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="EmpLocation" xdb:SQLName="EMP_LOCATION">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:maxLength value="25"/>
                      <xs:enumeration value="NewYork"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="EmpExperience" xdb:SQLName="EMP_EXPERIENCE">
                      <xs:simpleType>
                      <xs:restriction base="xs:positiveInteger">
                      <xs:totalDigits value="10"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      </xs:sequence>
                      </xs:complexType>
                      </xs:schema>

                      The sample XML file is as follows

                      <?xml version="1.0"?>
                      <Emp xsi:noNamespaceSchemaLocation="EMP.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xdb="http://xmlns.oracle.com/xdb">
                      <EmpNo>1001</EmpNo>
                      <FirstName>William</FirstName>
                      <LastName>Don</LastName>
                      <EmployeeType>Permanent</EmployeeType>
                      <EmpLocation>NewYork</EmpLocation>
                      <EmpExperience>10</EmpExperience>
                      </Emp>

                      The modified schema definition is as follows

                      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0" xdb:storeVarrayAsTable="true">
                      <xs:element name="Emp" type="EmpType" xdb:defaultTable="EMPLOYEES" xdb:columnProps="CONSTRAINT emp_pk PRIMARY KEY (XMLDATA.EMP_NO)" />
                      <xs:complexType name="EmpType" xdb:SQLType="EMPLOYEES_T">
                      <xs:sequence>
                      <xs:element name="EmpNo" xdb:SQLName="EMP_NO">
                      <xs:simpleType>
                      <xs:restriction base="xs:positiveInteger">
                      <xs:totalDigits value="10"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="FirstName" xdb:SQLName="FIRST_NAME">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:maxLength value="30"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="LastName" xdb:SQLName="LAST_NAME">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:maxLength value="30"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="EmployeeType" xdb:SQLName="EMPLOYEE_TYPE">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:length value="25"/>
                      <xs:enumeration value="Permanent"/>     
                      <xs:enumeration value="Consultant"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="EmpLocation" xdb:SQLName="EMP_LOCATION">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:maxLength value="25"/>
                      <xs:enumeration value="NewYork"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="EmpExperience" xdb:SQLName="EMP_EXPERIENCE">
                      <xs:simpleType>
                      <xs:restriction base="xs:positiveInteger">
                      <xs:totalDigits value="10"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      </xs:sequence>
                      </xs:complexType>
                      <xs:element name="WorkHrs" maxOccurs="100" xdb:SQLName="WorkHrs" xdb:propNumber="3280" xdb:global="false" xdb:SQLType="WorkHrs_T" xdb:SQLSchema="APPS" xdb:memType="258" xdb:SQLInline="true" xdb:MemInline="false" xdb:JavaInline="false" xdb:SQLCollType="WORKHRS330_COLL" xdb:SQLCollSchema="APPS">
                      <xs:complexType xdb:SQLType="WORKHRS_T" xdb:SQLSchema="APPS">
                      <xs:attribute name="Day" xdb:SQLName="DAY" xdb:propNumber="3273" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="2">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:maxLength value="20"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:attribute>
                      <xs:attribute name="Hrs" xdb:SQLName="HRS" xdb:propNumber="3274" xdb:global="false" xdb:SQLType="NUMBER" xdb:memType="2">
                      <xs:simpleType>
                      <xs:restriction base="xs:decimal"/>
                      </xs:simpleType>
                      </xs:attribute>
                      </xs:schema>

                      The sample XML file is as follows

                      <?xml version="1.0"?>
                      <Emp xsi:noNamespaceSchemaLocation="EMP.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xdb="http://xmlns.oracle.com/xdb">
                      <EmpNo>1001</EmpNo>
                      <FirstName>William</FirstName>
                      <LastName>Don</LastName>
                      <EmployeeType>Permanent</EmployeeType>
                      <EmpLocation>NewYork</EmpLocation>
                      <EmpExperience>10</EmpExperience>
                      <WorkHrs Day="Monday" Hrs="8.0"/>
                      <WorkHrs Day="Tuesday" Hrs="6.5"/>
                      <WorkHrs Day="Wednesday" Hrs="8.5"/>
                      <WorkHrs Day="Thursday" Hrs="10.5"/>
                      <WorkHrs Day="Friday" Hrs="5.5"/>
                      </Emp>

                      The Copy Evolve Procedure is as follows (I have added attributes to the above schema definition)

                      DECLARE

                      SCHEMA_TO_EVOLVE     XDB$STRING_LIST_T;
                      NEW_SCHEMA               XMLSEQUENCETYPE;
                      OLD_SCHEMA_URL          VARCHAR2(100) := 'EMP.xsd';
                      V_NEW_XML_DOC          CLOB := '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0" xdb:storeVarrayAsTable="true">
                      <xs:element name="Emp" type="EmpType" xdb:defaultTable="EMPLOYEES" xdb:columnProps="CONSTRAINT emp_pk PRIMARY KEY (XMLDATA.EMP_NO)" />
                      <xs:complexType name="EmpType" xdb:SQLType="EMPLOYEES_T">
                      <xs:sequence>
                      <xs:element name="EmpNo" xdb:SQLName="EMP_NO">
                      <xs:simpleType>
                      <xs:restriction base="xs:positiveInteger">
                      <xs:totalDigits value="10"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="FirstName" xdb:SQLName="FIRST_NAME">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:maxLength value="30"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="LastName" xdb:SQLName="LAST_NAME">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:maxLength value="30"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="EmployeeType" xdb:SQLName="EMPLOYEE_TYPE">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:length value="25"/>
                      <xs:enumeration value="Permanent"/>     
                      <xs:enumeration value="Consultant"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="EmpLocation" xdb:SQLName="EMP_LOCATION">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:maxLength value="25"/>
                      <xs:enumeration value="NewYork"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      <xs:element name="EmpExperience" xdb:SQLName="EMP_EXPERIENCE">
                      <xs:simpleType>
                      <xs:restriction base="xs:positiveInteger">
                      <xs:totalDigits value="10"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:element>
                      </xs:sequence>
                      </xs:complexType>
                      <xs:element name="WorkHrs" maxOccurs="100" xdb:SQLName="WorkHrs" xdb:propNumber="3280" xdb:global="false" xdb:SQLType="WorkHrs_T" xdb:SQLSchema="APPS" xdb:memType="258" xdb:SQLInline="true" xdb:MemInline="false" xdb:JavaInline="false" xdb:SQLCollType="WORKHRS330_COLL" xdb:SQLCollSchema="APPS">
                      <xs:complexType xdb:SQLType="WORKHRS_T" xdb:SQLSchema="APPS">
                      <xs:attribute name="Day" xdb:SQLName="DAY" xdb:propNumber="3273" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="2">
                      <xs:simpleType>
                      <xs:restriction base="xs:string">
                      <xs:maxLength value="20"/>
                      </xs:restriction>
                      </xs:simpleType>
                      </xs:attribute>
                      <xs:attribute name="Hrs" xdb:SQLName="HRS" xdb:propNumber="3274" xdb:global="false" xdb:SQLType="NUMBER" xdb:memType="2">
                      <xs:simpleType>
                      <xs:restriction base="xs:decimal"/>
                      </xs:simpleType>
                      </xs:attribute>
                      </xs:schema>';

                      BEGIN

                      DBMS_OUTPUT.PUT_LINE('B4 GETTING FILE FROM OS');

                      EXECUTE IMMEDIATE 'DROP TABLE EMPLOYEES_TEMP';

                      -- Getting file from the file system
                      --V_NEW_XML_DOC           := XXTIF_EDI_UTL.GET_CLOB_DOCUMENT('COIL_1.1.xsd','UTF8');

                      SCHEMA_TO_EVOLVE     := XDB$STRING_LIST_T(OLD_SCHEMA_URL);
                      NEW_SCHEMA           := XMLSEQUENCETYPE(XMLTYPE(V_NEW_XML_DOC));

                      DBMS_XMLSCHEMA.COPYEVOLVE(SCHEMA_TO_EVOLVE,NEW_SCHEMA, preserveOldDocs => TRUE, mapTabName => 'EMPLOYEES_TEMP', generateTables => FALSE);
                      COMMIT;

                      DBMS_OUTPUT.PUT_LINE('SUCCESSFULLY COPIED');

                      EXCEPTION
                      WHEN OTHERS THEN
                      DBMS_OUTPUT.PUT_LINE('THE ERROR IS '||SQLERRM);
                      END;
                      /

                      The Table Creation is as follows

                      CREATE TABLE EMPLOYEES (XMLDOC XMLTYPE,
                                     CREATE_DATE DATE DEFAULT SYSDATE NOT NULL ,
                      CONSTRAINT emp_pk PRIMARY KEY (XMLDOC."XMLDATA".EMP_NO)
                      )
                      XMLTYPE COLUMN XMLDOC STORE AS OBJECT RELATIONAL
                      XMLSCHEMA "EMP.xsd" ELEMENT "Emp"
                      /


                      DECLARE

                      TABLENAME VARCHAR2(2000) := 'EMPLOYEES1128';

                      BEGIN

                      SELECT TEMP_TABNAME
                      INTO TABLENAME
                      FROM EMPLOYEES_TEMP
                      WHERE TABLE_NAME = USER || '.' || UPPER('RELATIE_DOCUMENTEN');

                      EXECUTE IMMEDIATE 'INSERT INTO EMPLOYEES(XMLDOC) SELECT XMLTYPE(DATA) FROM ' || TABLENAME;

                      COMMIT;

                      END;
                      /

                      When I executed the above SQL, It only populates main data(Perosnal Details such as EmpNo,FirstName,LastName) but not attributes (Workhrs.Day,WOrkhrs.Hrs).

                      Please let me know, how can I move those attributes data?

                      Again Oracle & XML versions are as follows

                      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
                      PL/SQL Release 10.2.0.3.0 - Production
                      CORE 10.2.0.3.0 Production

                      Oracle XML Database 10.2.0.3.0

                      Thanks in Advance.
                      • 8. Re: How to create default column in XML schema ?
                        odie_63
                        Hi,

                        The new schema you posted is not wellformed, see at the bottom, missing closing tags for <complexType> and <element>.

                        I also strongly suggest you do not use the internal version of the schema with all specifics annotations (xdb:propNumber, xdb:memType etc.), I've already seen that causing conflicts.
                        Start from the old schema, add the new part and post again a clean version here, please.
                        It only populates main data(Perosnal Details such as EmpNo,FirstName,LastName) but not attributes (Workhrs.Day,WOrkhrs.Hrs).
                        Sorry if I'm missing something but how could you possibly populate the new attributes from backed-up XML documents conforming to the old schema?

                        Edited by: odie_63 on 1 mars 2013 13:13
                        • 9. Re: How to create default column in XML schema ?
                          odie_63
                          Here's something that works for me on 10.2.0.5 :

                          1) Old schema

                          Please note the following points :
                          - since you create the target table manually, you don't need the "xdb:defaultTable", "xdb:columnProps" and "xdb:storeVarrayAsTable" annotations --> I removed them.
                          - the "length" attribute for the EmployeeType element is not compatible with the enumeration list --> I changed it to "maxLength".
                          <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0">
                            <xs:element name="Emp" type="EmpType" xdb:defaultTable="EMPLOYEES"/>
                            <xs:complexType name="EmpType" xdb:SQLType="EMPLOYEES_T">
                              <xs:sequence>
                                <xs:element name="EmpNo" xdb:SQLName="EMP_NO">
                                  <xs:simpleType>
                                    <xs:restriction base="xs:positiveInteger">
                                      <xs:totalDigits value="10"/>
                                    </xs:restriction>
                                  </xs:simpleType>
                                </xs:element>
                                <xs:element name="FirstName" xdb:SQLName="FIRST_NAME">
                                  <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                      <xs:maxLength value="30"/>
                                    </xs:restriction>
                                  </xs:simpleType>
                                </xs:element>
                                <xs:element name="LastName" xdb:SQLName="LAST_NAME">
                                  <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                      <xs:maxLength value="30"/>
                                    </xs:restriction>
                                  </xs:simpleType>
                                </xs:element>
                                <xs:element name="EmployeeType" xdb:SQLName="EMPLOYEE_TYPE">
                                  <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                      <xs:maxLength value="25"/>
                                      <xs:enumeration value="Permanent"/>
                                      <xs:enumeration value="Consultant"/>
                                    </xs:restriction>
                                  </xs:simpleType>
                                </xs:element>
                                <xs:element name="EmpLocation" xdb:SQLName="EMP_LOCATION">
                                  <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                      <xs:maxLength value="25"/>
                                      <xs:enumeration value="NewYork"/>
                                    </xs:restriction>
                                  </xs:simpleType>
                                </xs:element>
                                <xs:element name="EmpExperience" xdb:SQLName="EMP_EXPERIENCE">
                                  <xs:simpleType>
                                    <xs:restriction base="xs:positiveInteger">
                                      <xs:totalDigits value="10"/>
                                    </xs:restriction>
                                  </xs:simpleType>
                                </xs:element>
                              </xs:sequence>
                            </xs:complexType>
                          </xs:schema>
                          Registration :
                          begin
                           
                            dbms_xmlschema.registerSchema(
                              schemaURL => 'EMP.xsd'
                            , schemaDoc => :v_old_schema
                            , local => true
                            , genTypes => true
                            , genTables => false
                            , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
                            ) ;
                            
                          end;
                          /
                          Test :
                          SQL> CREATE TABLE EMPLOYEES (XMLDOC XMLTYPE,
                            2  CREATE_DATE DATE DEFAULT SYSDATE NOT NULL ,
                            3  CONSTRAINT emp_pk PRIMARY KEY (XMLDOC."XMLDATA".EMP_NO)
                            4  )
                            5  XMLTYPE COLUMN XMLDOC STORE AS OBJECT RELATIONAL
                            6  XMLSCHEMA "EMP.xsd" ELEMENT "Emp" ;
                           
                          Table created
                           
                          SQL> 
                          SQL> insert into employees (xmldoc) values(
                            2  xmltype(
                            3  '<?xml version="1.0"?>
                            4  <Emp xsi:noNamespaceSchemaLocation="EMP.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                            5  <EmpNo>1001</EmpNo>
                            6  <FirstName>William</FirstName>
                            7  <LastName>Don</LastName>
                            8  <EmployeeType>Permanent</EmployeeType>
                            9  <EmpLocation>NewYork</EmpLocation>
                           10  <EmpExperience>10</EmpExperience>
                           11  </Emp>')
                           12  );
                           
                          1 row inserted
                           
                          SQL> select create_date, xmlisvalid(xmldoc) from employees ;
                           
                          CREATE_DATE XMLISVALID(XMLDOC)
                          ----------- ------------------
                          01/03/2013                   1
                           
                          2) The new schema

                          Since old XML instances have to conform to the new schema, the new element "WorkHrs" must have minOccurs="0" :
                          <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0">
                            <xs:element name="Emp" type="EmpType"/>
                            <xs:complexType name="EmpType" xdb:SQLType="EMPLOYEES_T">
                              <xs:sequence>
                                <xs:element name="EmpNo" xdb:SQLName="EMP_NO">
                                  <xs:simpleType>
                                    <xs:restriction base="xs:positiveInteger">
                                      <xs:totalDigits value="10"/>
                                    </xs:restriction>
                                  </xs:simpleType>
                                </xs:element>
                                <xs:element name="FirstName" xdb:SQLName="FIRST_NAME">
                                  <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                      <xs:maxLength value="30"/>
                                    </xs:restriction>
                                  </xs:simpleType>
                                </xs:element>
                                <xs:element name="LastName" xdb:SQLName="LAST_NAME">
                                  <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                      <xs:maxLength value="30"/>
                                    </xs:restriction>
                                  </xs:simpleType>
                                </xs:element>
                                <xs:element name="EmployeeType" xdb:SQLName="EMPLOYEE_TYPE">
                                  <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                      <xs:maxLength value="25"/>
                                      <xs:enumeration value="Permanent"/>
                                      <xs:enumeration value="Consultant"/>
                                    </xs:restriction>
                                  </xs:simpleType>
                                </xs:element>
                                <xs:element name="EmpLocation" xdb:SQLName="EMP_LOCATION">
                                  <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                      <xs:maxLength value="25"/>
                                      <xs:enumeration value="NewYork"/>
                                    </xs:restriction>
                                  </xs:simpleType>
                                </xs:element>
                                <xs:element name="EmpExperience" xdb:SQLName="EMP_EXPERIENCE">
                                  <xs:simpleType>
                                    <xs:restriction base="xs:positiveInteger">
                                      <xs:totalDigits value="10"/>
                                    </xs:restriction>
                                  </xs:simpleType>
                                </xs:element>
                                <xs:element name="WorkHrs" xdb:SQLName="WORKHRS" minOccurs="0" maxOccurs="100" xdb:SQLType="WORKHRS_T" xdb:SQLCollType="WORKHRS_COLL">
                                  <xs:complexType>
                                    <xs:attribute name="Day" xdb:SQLName="DAY">
                                      <xs:simpleType>
                                        <xs:restriction base="xs:string">
                                          <xs:maxLength value="20"/>
                                        </xs:restriction>
                                      </xs:simpleType>
                                    </xs:attribute>
                                    <xs:attribute name="Hrs" xdb:SQLName="HRS">
                                      <xs:simpleType>
                                        <xs:restriction base="xs:decimal"/>
                                      </xs:simpleType>
                                    </xs:attribute>
                                  </xs:complexType>
                                </xs:element>
                              </xs:sequence>
                            </xs:complexType>
                          </xs:schema>
                          Schema evolution :
                          DECLARE
                          
                            v_new_schema xmltype := xmltype(:v_new_schema);
                          
                          BEGIN
                          
                            dbms_xmlschema.CopyEvolve(
                              schemaURLs => xdb$string_list_t('EMP.xsd')
                            , newSchemas => xmlsequencetype(v_new_schema)
                            , preserveOldDocs => true
                            , mapTabName => 'EMPLOYEES_TEMP'
                            , generateTables => false
                            );
                          
                          END;
                          /
                          Inserting backed-up data :
                          SQL> select * from employees_temp;
                           
                          SCHEMA_URL     SCHEMA_OWNER    ELEMENT_NAME   TABLE_NAME       TABLE_OID   COLUMN_NAME   TEMP_TABNAME
                          -------------- --------------- -------------- ---------------- ----------- ------------- ---------------
                          EMP.xsd        MARC            Emp            MARC.EMPLOYEES               XMLDOC        EMPLOYEES271
                           
                          
                          SQL> CREATE TABLE EMPLOYEES (XMLDOC XMLTYPE,
                            2  CREATE_DATE DATE DEFAULT SYSDATE NOT NULL ,
                            3  CONSTRAINT emp_pk PRIMARY KEY (XMLDOC."XMLDATA".EMP_NO)
                            4  )
                            5  XMLTYPE COLUMN XMLDOC STORE AS OBJECT RELATIONAL
                            6  XMLSCHEMA "EMP.xsd" ELEMENT "Emp" ;
                           
                          Table created
                           
                          SQL> insert into employees (xmldoc) select xmltype(data) from employees271;
                           
                          1 row inserted
                           
                          Adding a new document :
                          SQL> insert into employees (xmldoc) values(
                            2  xmltype(
                            3  '<?xml version="1.0"?>
                            4  <Emp xsi:noNamespaceSchemaLocation="EMP.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                            5  <EmpNo>1002</EmpNo>
                            6  <FirstName>William</FirstName>
                            7  <LastName>Don</LastName>
                            8  <EmployeeType>Permanent</EmployeeType>
                            9  <EmpLocation>NewYork</EmpLocation>
                           10  <EmpExperience>10</EmpExperience>
                           11  <WorkHrs Day="Monday" Hrs="8.0"/>
                           12  <WorkHrs Day="Tuesday" Hrs="6.5"/>
                           13  <WorkHrs Day="Wednesday" Hrs="8.5"/>
                           14  <WorkHrs Day="Thursday" Hrs="10.5"/>
                           15  <WorkHrs Day="Friday" Hrs="5.5"/>
                           16  </Emp>')
                           17  );
                           
                          1 row inserted
                           
                          SQL> 
                          SQL> select x."EmpNo", h.*
                            2  from employees e
                            3     , xmltable('/Emp' passing e.xmldoc
                            4         columns "EmpNo" number(10)
                            5               , "WorkHrs" xmltype
                            6       ) x
                            7       left outer join
                            8       xmltable('/WorkHrs' passing x."WorkHrs"
                            9         columns work_day varchar2(20) path '@Day'
                           10               , work_hrs number       path '@Hrs'
                           11       ) h
                           12       on 1 = 1
                           13  ;
                           
                                EmpNo WORK_DAY               WORK_HRS
                          ----------- -------------------- ----------
                                 1001                      
                                 1002 Monday                        8
                                 1002 Tuesday                     6,5
                                 1002 Wednesday                   8,5
                                 1002 Thursday                   10,5
                                 1002 Friday                      5,5
                           
                          6 rows selected
                           
                          • 10. Re: How to create default column in XML schema ?
                            794743
                            Hi Odie,

                            Thanks a lot for the solution offered. I will apply the same.

                            Also, to know, For Example, If attributes are backed(During CopyEvolve) up already,I hope your suggested sql
                            would retrieve the attributes data from Employees table.

                            select x."EmpNo", h.*
                            from employees e
                            , xmltable('/Emp' passing e.xmldoc
                            columns "EmpNo" number(10)
                            , "WorkHrs" xmltype
                            ) x
                            left outer join
                            xmltable('/WorkHrs' passing x."WorkHrs"
                            columns work_day varchar2(20) path '@Day'
                            , work_hrs number path '@Hrs'
                            ) h
                            on 1 = 1

                            Please correct me, If I am wrong.

                            Thanks,
                            • 11. Re: How to create default column in XML schema ?
                              odie_63
                              Also, to know, For Example, If attributes are backed(During CopyEvolve) up already,I hope your suggested sql
                              would retrieve the attributes data from Employees table.
                              You didn't answer my previous question.

                              How could you have <WorkHrs> elements in old XML documents if there are not defined in the old schema?
                              • 12. Re: How to create default column in XML schema ?
                                794743
                                Hi Odie,

                                That is not possible to retain it in this situation, I agree.
                                But what happens, If it exists and would you suggest to the use the above SQL?


                                Thanks,
                                • 13. Re: How to create default column in XML schema ?
                                  odie_63
                                  But what happens, If it exists and would you suggest to the use the above SQL?
                                  Yes, but again it depends on how you want to present the result.
                                  If you're happy with the query output, then use it, otherwise please show us how you want to extract the attributes.
                                  • 14. Re: How to create default column in XML schema ?
                                    794743
                                    Hi Odie,

                                    Thanks for the update and sorry for the confusion.
                                    I will close this thread and have updated already in other thread.

                                    Once again, Thanks for your support.