5 Replies Latest reply: Jan 7, 2013 2:56 PM by Robo [Maind]-Oracle RSS

    Simple node with attribute

    Robo [Maind]-Oracle
      Thanks for reading

      1. i register scheme like this
        schemaurl VARCHAR2(256);
        xmlschema xmltype;
        schemaurl := 'ACTLOGLINEList.xsd';
        xmlschema := xmltype(
      '<?xml version="1.0" encoding="UTF-8"?>
      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
      xmlns:xdb="http://xmlns.oracle.com/xdb" xdb:storeVarrayAsTable="true" >
        <xs:element name="ACTLOGLINE" type="ACTLOGLINEListType" xdb:defaultTable="TMP_SPM_ACTLOGLINE"/>
        <xs:complexType name="ACTLOGLINEListType"  xdb:maintainDOM="false" xdb:SQLType="ACTLOGLINE_LIST_T">
            <xs:element name="ACTLOGLINE" type="ACTLOGLINEType" maxOccurs="unbounded" xdb:SQLCollType="ACTLOGLINE_V"/>
        <xs:complexType name="ACTLOGLINEType" xdb:maintainDOM="false" xdb:SQLType="ACTLOGLINE_T">
           <xs:element name="DTTIMESTAMP" type="xs:string"/>
        dbms_xmlschema.registerschema(schemaurl => schemaurl
                                     ,schemadoc => xmlschema
                                     ,LOCAL     => TRUE
                                     ,gentypes  => TRUE
                                     ,genbean   => FALSE
                                     ,gentables => TRUE);
      2. i try to insert XML
       insert into TMP_SPM_ACTLOGLINE values (xmltype(
        '<ACTLOGLINE xmlns:xs="http://www.w3.org/2001/XMLSchema" 
              <DTTIMESTAMP xsi:type="xsd:string">41262.718611111111</DTTIMESTAMP>
      3. get error ORA-31187 Cannot Add Node type (type=attribute) to Simple Type Node /ACTLOGLINE/ACTLOGLINE[1]/DTTIMESTAMP
      In know that without xsi:type="xsd:string" the code will work but unfortunately i am not able to change the look of source xml.

      The question is how should the scheme look like to insert this XML?

      any help appreciated
        • 1. Re: Simple node with attribute
          Three thoughts:

          1. What Oracle version number?
          SELECT * FROM v$version;
          2. Please provide the DDL for the table so we can build it.

          3. Please read the FAQ and learn how to use
           tags to format your listings and make them easier to read.
          Perhaps then we can help you.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
          • 2. Re: Simple node with attribute
            Robo [Maind]-Oracle
            2. table is created within dbms_xmlschema.registerschema, see code above
            3. thanks for hint, i could not find it in "Plain Text Help" before

            Any help?

            Edited by: Robo [Maind] on Jan 5, 2013 1:00 PM
            • 3. Re: Simple node with attribute
              It looks very much like a bug.
              Although the local type redefinition is not needed, the validation should be OK.

              Do you expect other types besides xsi:type="xsd:string" ?

              I tried with an abstract content and an xs:anyAttribute instead but got another error : ORA-31079: unable to resolve reference to type "string".

              One possible but bad workaround would be to remove the xsi:type attribute prior to validation :
                '<ACTLOGLINE xmlns:xs="http://www.w3.org/2001/XMLSchema" 
                      <DTTIMESTAMP xsi:type="xsd:string">41262.718611111111</DTTIMESTAMP>
                  , '/ACTLOGLINE/ACTLOGLINE/DTTIMESTAMP/@xsi:type'
                  , 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"'
              • 4. Re: Simple node with attribute
                Would both of you please open SRs with MyOracleSupport so we can get this fixed?

                But before you do ... there is really no excuse for having installed unless this is your home education server. Patch to and see if the issue has already been fixed.

                Thank you.
                • 5. Re: Simple node with attribute
                  Robo [Maind]-Oracle
                  Thanks for your help.

                  I tried the same on version and the same result.

                  Delete of attributes would cause performance issues and performance was the main goal of trying XDB.

                  I cannot see the create link for SR on my MyOracleSupport ?