This discussion is archived
14 Replies Latest reply: Mar 3, 2013 9:20 AM by 794743 RSS

How to create default column in XML schema ?

794743 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi Odie,

    Thanks a lot. Let me try this approach.

    Regards,
  • 5. Re: How to create default column in XML schema ?
    794743 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

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