7 Replies Latest reply: Jul 2, 2012 11:38 AM by 945165 RSS

    XML DB XPath error

    945165
      I've loaded an XML file and schema using XML DB, but am having trouble with EXTRACTVALUE evaluating a valid XPath expression. The following query returns an error:

      SELECT EXTRACTVALUE(xseq.column_name,
      '/ContainerSet/SequenceContainer[4]/BaseContainer/RestrictionCriteria/ComparisonList[2]/@value') AS value
      FROM xtce xx
      , TABLE(XMLSEQUENCE(EXTRACT(VALUE(xx), '/SpaceSystem/TelemetryMetaData/ContainerSet'))) xseq;

      Error at line 2:
      ORA-00904: "RESTRICTIONCRITERIA10682_TAB"."SYS_NC0001700018$": invalid identifier

      However, this query works:

      SELECT EXTRACTVALUE(xseq.column_name,
      '/ContainerSet/SequenceContainer[4]/BaseContainer/RestrictionCriteria/ComparisonList[@parameterRef="ContentID"]/@value') AS value
      FROM xtce xx
      , TABLE(XMLSEQUENCE(EXTRACT(VALUE(xx), '/SpaceSystem/TelemetryMetaData/ContainerSet'))) xseq;

      VALUE
      ---------
      5

      Both XPath expressions work fine when run through a Java XML parser.

      Evidently it is sometimes permissible to use the actual index number and sometimes not as it worked on SequenceContainer in both cases. Is there a predicatble way to know when this can and can't be done?

      The relevant part of the XML file is listed below:

      <xtce:BaseContainer containerRef="DEMTelemetryPacket">
      <xtce:RestrictionCriteria>
      <xtce:ComparisonList>
      <xtce:Comparison value="200" parameterRef="SystemID"/>
      <xtce:Comparison value="5" parameterRef="ContentID"/>
      <xtce:Comparison value="0" parameterRef="ContentMapID"/>
      <xtce:Comparison value="0" parameterRef="CRC"/>
      </xtce:ComparisonList>
      </xtce:RestrictionCriteria>
      </xtce:BaseContainer>
        • 1. Re: XML DB XPath error
          odie_63
          Hi,

          A couple of remarks you may want to explain first :

          1- The XPath expression doesn't match the XML fragment you gave.
          2- "xseq.column_name" ? Are you sure it's not "COLUMN_VALUE"?
          Evidently it is sometimes permissible to use the actual index number and sometimes not
          Not so "evidently" IMO.
          It should work in any case, positional predicates are rewritten to access the ARRAY_INDEX of the corresponding nested table.

          Could you give the command you used to register the schema?
          Did you specify genTables => true ?

          Hoping I could reproduce I've registered the schema from here : http://www.omg.org/spec/XTCE/20061101/06-11-06.xsd
          and loaded a sample document found in an XTCE tutorial (adding the ComparisonList fragment) :
          <?xml version="1.0" encoding="UTF-8"?>
          <SpaceSystem xmlns="http://www.omg.org/space/xtce" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.omg.org/space/xtce SpaceSystemV1.1.xsd" name="TrivialSat3">
               <TelemetryMetaData>
                    <ParameterTypeSet>
                         <FloatParameterType name="Battery1VoltageType">
                              <UnitSet/>
                              <IntegerDataEncoding/>
                         </FloatParameterType>
                         <EnumeratedParameterType name="BeaconStatusType">
                              <UnitSet/>
                              <IntegerDataEncoding/>
                              <EnumerationList>
                                   <Enumeration value="128" label="On"/>
                                   <Enumeration value="0" label="Off"/>
                              </EnumerationList>
                         </EnumeratedParameterType>
                         <BinaryParameterType name="minorFrameCtrType">
                              <UnitSet/>
                         </BinaryParameterType>
                    </ParameterTypeSet>
                    <ParameterSet>
                         <Parameter name="BatteryVoltage" parameterTypeRef="BatteryVoltageType"/>
                         <Parameter name="BeaconStatus" parameterTypeRef="BeaconStatusType"/>
                         <Parameter name="minorFrameCtr" parameterTypeRef="minorFrameCtrType"/>
                    </ParameterSet>
                    <ContainerSet>
                         <SequenceContainer name="MinorFrame">
                              <EntryList>
                                   <ParameterRefEntry parameterRef="minorFrameCtr"/>
                              </EntryList>
                         </SequenceContainer>
                         <SequenceContainer name="MinorFrame0">
                              <EntryList>
                                   <ParameterRefEntry parameterRef="Battery1Voltage"/>
                                   <ParameterRefEntry parameterRef="BeaconStatus"/>
                              </EntryList>
                              <BaseContainer containerRef="MinorFrame">
                                   <RestrictionCriteria>
                                        <Comparison parameterRef="minorFrameCtr" value="0x00"/>
                                   </RestrictionCriteria>
                              </BaseContainer>
                         </SequenceContainer>
                         <SequenceContainer name="MinorFrame1">
                              <EntryList>
                                   <ParameterRefEntry parameterRef="BeaconStatus"/>
                                   <ParameterRefEntry parameterRef="Battery1Voltage"/>
                              </EntryList>
                              <BaseContainer containerRef="MinorFrame">
                                   <RestrictionCriteria>
                      <ComparisonList>
                        <Comparison value="200" parameterRef="SystemID"/>
                        <Comparison value="5" parameterRef="ContentID"/>
                        <Comparison value="0" parameterRef="ContentMapID"/>
                        <Comparison value="0" parameterRef="CRC"/>
                      </ComparisonList>
                                   </RestrictionCriteria>
                              </BaseContainer>
                         </SequenceContainer>
                    </ContainerSet>
               </TelemetryMetaData>
               <CommandMetaData>
                    <ArgumentTypeSet>
                         <BinaryArgumentType name="opCodeType">
                              <UnitSet/>
                         </BinaryArgumentType>
                         <EnumeratedArgumentType name="onOffType">
                              <UnitSet/>
                              <EnumerationList>
                                   <Enumeration value="1" label="on"/>
                                   <Enumeration value="0" label="off"/>
                              </EnumerationList>
                         </EnumeratedArgumentType>
                    </ArgumentTypeSet>
                    <MetaCommandSet>
                         <MetaCommand name="TrivialSatCmdType" abstract="true">
                              <ArgumentList>
                                   <Argument name="opCode" argumentTypeRef="opCodeType"/>
                              </ArgumentList>
                              <CommandContainer name="TrivialSatCommandContainerType">
                                   <EntryList>
                                        <ArgumentRefEntry argumentRef="opCode"/>
                                   </EntryList>
                              </CommandContainer>
                         </MetaCommand>
                         <MetaCommand name="BeaconPwr">
                              <LongDescription>Turn Beacon Power on or off</LongDescription>
                              <BaseMetaCommand metaCommandRef="TrivialSatCmdType">
                                   <ArgumentAssignmentList>
                                        <ArgumentAssignment argumentName="opCode" argumentValue="170"/>
                                   </ArgumentAssignmentList>
                              </BaseMetaCommand>
                              <ArgumentList>
                                   <Argument name="powerValue" argumentTypeRef="onOffType"/>
                              </ArgumentList>
                              <CommandContainer name="BeaconPwr">
                                   <EntryList>
                                        <ArgumentRefEntry argumentRef="powerValue"/>
                                   </EntryList>
                                   <BaseContainer containerRef="TrivialSatCmdContianerType">
                                   </BaseContainer>
                              </CommandContainer>
                         </MetaCommand>
                         <MetaCommand name="BeaconPwrOn">
                              <LongDescription/>
                              <BaseMetaCommand metaCommandRef="BeaconPwr">
                                   <ArgumentAssignmentList>
                                        <ArgumentAssignment argumentName="powerValue" argumentValue="on"/>
                                   </ArgumentAssignmentList>
                              </BaseMetaCommand>
                         </MetaCommand>
                    </MetaCommandSet>
               </CommandMetaData>
          </SpaceSystem>
          This works for me :
          SQL> SELECT EXTRACTVALUE(xseq.column_value,
            2  '/ContainerSet/SequenceContainer[3]/BaseContainer/RestrictionCriteria/ComparisonList/Comparison[2]/@value') AS value
            3  FROM "SpaceSystem1167_TAB" xx
            4     , TABLE(XMLSEQUENCE(EXTRACT(VALUE(xx), '/SpaceSystem/TelemetryMetaData/ContainerSet'))) xseq
            5  WHERE xmlexists(
            6  'declare default element namespace "http://www.omg.org/space/xtce"; /SpaceSystem[@name="TrivialSat3"]'
            7  passing xx.object_value
            8  );
           
          VALUE
          --------------------------------------------------------------------------------
          5
           
          I'm on 11.2.0.2, what's your exact db version?

          You should also try using non-deprecated functions instead :
          SQL> SELECT XMLCast(
            2           XMLQuery(
            3           'declare default element namespace "http://www.omg.org/space/xtce"; (: :)
            4            /SpaceSystem/TelemetryMetaData/ContainerSet/SequenceContainer[3]/BaseContainer/RestrictionCriteria/ComparisonList/Comparison[2]/@value'
            5            passing x.object_value
            6            returning content
            7           )
            8           as varchar2(4000)
            9         ) as value
           10  FROM "SpaceSystem1167_TAB" x
           11  WHERE XMLExists(
           12        'declare default element namespace "http://www.omg.org/space/xtce"; (: :)
           13        /SpaceSystem[@name="TrivialSat3"]'
           14        passing x.object_value
           15        )
           16  ;
           
          VALUE
          --------------------------------------------------------------------------------
          5
           
          • 2. Re: XML DB XPath error
            945165
            odie_63:

            Thanks for responding, and for putting so much work into reproducing my problem. We're struggling here, as we're all new to XML DB.

            To answer your questions:

            1. Cut-n-paste error: should have been .../ComparisonList/Comparison[2]/... for both expressions.
            2. Yes, should have been "xseq.column_value"

            Command to register schema:

            DBMS_XMLSCHEMA.REGISTERSCHEMA(
            schemaurl => 'http://localhost:8080/public/xtcedev/xsd/xtce.xsd',
            schemadoc => sys.UriFactory.getUri('/public/xtcedev/xsd/xtce.xsd'));

            I'm using 11g, 11.2.0.3.0, 64-bit.

            And thank you for the heads-up on the deprecated functions.
            • 3. Re: XML DB XPath error
              945165
              addendum:

              we modified the xtce schema slightly to generate a consistent table name (we've bene through the loading/doesn't work/start again drill several times):

              <schema xmlns:xtce="http://www.omg.org/space/xtce" xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.omg.org/space/xtce" elementFormDefault="qualified" attributeFormDefault="unqualified" version="1.1">

              ...

              <element name="SpaceSystem" type="xtce:SpaceSystemType" nillable="true" xdb:defaultTable="XTCE">

              ...

              <complexType name="SpaceSystemType" mixed="false" xdb:SQLType="XTCE_TYPE" xdb:maintainDOM="false">
              • 4. Re: XML DB XPath error
                odie_63
                And thank you for the heads-up on the deprecated functions.
                Does it make a difference with XMLCast/XMLQuery instead?

                I'll try to reproduce on your version this week-end.
                • 5. Re: XML DB XPath error
                  945165
                  No, I get the same error with XMLCast/XMLQuery. I also tried adding the "WHERE xmlexists" clause to my query, with the same results.
                  • 6. Re: XML DB XPath error
                    odie_63
                    I used XMLExists because I had inserted multiple instance documents and just want to select one.

                    I still don't reproduce on 11.2.0.3.
                    I've annotated the schema with defaultTable and type name and used the same document as above :
                    -- schema registration command (took ~ 10 min) : 
                    begin
                     dbms_xmlschema.registerSchema(
                       schemaURL => 'SpaceSystemV1.1.xsd'
                     , schemaDoc => xmltype(bfilename('TEST_DIR','SpaceSystemV1.1.xsd'), nls_charset_id('AL32UTF8'))
                     , local => true
                     , genTypes => true
                     , genTables => true
                     , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
                     );
                    end;
                    /
                    
                    insert into xtce_table values(
                    xmltype(bfilename('TEST_DIR','SpaceSystem.xml'), nls_charset_id('AL32UTF8'))
                    );
                    SQL> select * from v$version;
                     
                    BANNER
                    --------------------------------------------------------------------------------
                    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
                    PL/SQL Release 11.2.0.3.0 - Production
                    CORE     11.2.0.3.0     Production
                    TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
                    NLSRTL Version 11.2.0.3.0 - Production
                     
                    SQL> 
                    SQL> SELECT XMLCast(
                      2           XMLQuery(
                      3            'declare default element namespace "http://www.omg.org/space/xtce"; (: :)
                      4             /SpaceSystem/TelemetryMetaData/ContainerSet/SequenceContainer[3]/BaseContainer/RestrictionCriteria/ComparisonList/Comparison[2]/@value'
                      5            passing x.object_value
                      6            returning content
                      7           )
                      8           as varchar2(4000)
                      9         ) as value
                     10  FROM xtce_table x
                     11  ;
                     
                    VALUE
                    --------------------------------------------------------------------------------
                    5
                     
                    Maybe it's an issue with a particular content.
                    Is it possible you post an instance on which you're having the problem? (might be a simplified doc, provided it produces the error)

                    Thanks.
                    • 7. Re: XML DB XPath error
                      945165
                      The XML file is rather large and contains some proprietary data, so it would likely take some work to get an acceptable subset that reprodcuced the error. One of my team members was able to sidestep the problem and do what we needed using XMLTable.

                      I really appreciate the time you put into helping us, and I hate to bail but we need to move forward. I might revisit the issue again when I have more time.

                      Thanks again!