8 Replies Latest reply: Jul 16, 2012 4:41 PM by 878240 RSS

    Not getting data from XML table

    878240
      I am able to load a file into an XML table but no data gets returned.

      This is the instance file.

      <?xml version="1.0" encoding="utf-8"?>
      <sam:SamseMonthly xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sam="http://samsemonthly.org/namespace">
      <AviationUIC>
      <OrigUnit>
      <UIC>ZABCD0</UIC>
      <UnitName>ABC</UnitName>
      <OrgLocCde>A</OrgLocCde>
      <DPICode>Need DPICode</DPICode>
      <IsReporter>true</IsReporter>
      <UnitPOC />
      <POCNbr>(855)999-1000</POCNbr>
      </OrigUnit>
      <Equipment>
      <INC>A</INC>
      <FSCCage />
      <NIINPNO />
      <WorkUnitCde>AAAAAA</WorkUnitCde>
      <EIC>***</EIC>
      <SerialNbr>A12345</SerialNbr>
      <EquipData>
      <Model>OH-58D</Model>
      <Nomenclature>LOW SPEED MOTOR</Nomenclature>
      </EquipData>
      </Equipment>
      </AviationUIC>
      </sam:SamseMonthly>


      create table samseMonthly
      (samseMonthly XMLType)
      XMLType COLUMN SAMSEMonthly
      XMLSCHEMA "SamseMonthly.xsd"
      ELEMENT "SamseMonthly";

      INSERT INTO samseMonthly
      VALUES (
      XMLType(
      bfilename('XML_DIR', 'SAMSEMonthly2.xml'),
      NLS_CHARSET_ID('UTF8'))
      );

      1 row created.

      Both of these methods return no data for any column.

      SELECT
      EXTRACTVALUE(SamseMonthly,
      '/SamseMonthly/AviationUIC/OrigUnit/UIC') as UIC
      FROM samseMonthly ;


      SELECT extract(SamseMonthly, '/SamseMonthly/FileInfo/Submitter/UIC') as UIC
      FROM samseMonthly ;

      or

      SELECT nvl(uic,'ABC') as UIC
      from (
      SELECT
      EXTRACTVALUE(SamseMonthly,
      '/SamseMonthly/AviationUIC/OrigUnit/UIC') as UIC
      FROM samseMonthly
      );

      UIC
      ------
      ABC


      This is part of the schema:

      <?xml version="1.0" encoding="UTF-8"?>
      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="unqualified" attributeFormDefault="unqualified"
      targetNamespace="http://samsemonthly.org/namespace"
      xmlns:samnth="http://samsemonthly.org/namespace"
      xmlns:xdb="http://xmlns.oracle.com/xdb"
      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:smse="http://samsecommon.xsd/namespace" >
      <xsd:import namespace="http://samsecommon.xsd/namespace" schemaLocation="samsecommon.xsd"/>
      <xs:element name="SamseMonthly" xdb:defaultTable="SAMSEMONTHLY" xdb:SQLName="SamseMonthly">
      <xs:annotation>
      <xs:documentation>Root</xs:documentation>
      </xs:annotation>
      <xs:complexType xdb:SQLType="SamseMonthly_T">
      <xs:sequence>
      <xs:element name="FileInfo" minOccurs="0" smse:type="SamseFileInfoType"/>
      <xs:element name="AviationUIC" nillable="true" maxOccurs="unbounded" xdb:SQLName="AviationUIC" xdb:defaultTable="AviationUIC">
      <xs:annotation>
      <xs:documentation>1 element for each UIC being reported (multiple UICs from 2E box)</xs:documentation>
      </xs:annotation>
      <xs:complexType xdb:SQLType="AviationUIC_T">
      <xs:sequence>
      <xs:element name="OrigUnit" nillable="true" xdb:SQLName="OrigUnit">
      <xs:complexType xdb:SQLType="OrigUnit_T">
      <xs:sequence>
      <xs:element name="UIC" nillable="true" smse:type="UICType" xdb:SQLName="UIC">
      <xs:annotation>
      <xs:documentation>Key Field</xs:documentation>
      </xs:annotation>
      </xs:element>
      <xs:element name="CRTFMT" minOccurs="0" smse:type="CRTFMTType" nillable="true" xdb:SQLName="CRTFMT">
      <xs:annotation>
      <xs:documentation>Key Field when non-null</xs:documentation>
      </xs:annotation>
      </xs:element>
      <xs:element name="UnitName" nillable="true" xdb:SQLName="UnitName">
      <xs:simpleType>
      <xs:restriction base="xs:token">
      <xs:minLength value="1"/>
      <xs:maxLength value="80"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="OrgLocCde" nillable="true" smse:type="OrgLocCdeEnum" xdb:SQLName="OrgLocCde"/>
      <xs:element name="UtilCde" minOccurs="0" nillable="true" xdb:SQLName="UtilCde">
      <xs:simpleType>
      <xs:restriction base="xs:token">
      <xs:length value="1"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="DPICode" smse:type="DPICodeType" nillable="true" xdb:SQLName="DPICode"/>
      <xs:element name="IsReporter" nillable="true" type="xs:boolean" xdb:SQLName="IsReporter"/>
      <xs:element name="UnitPOC" xdb:SQLName="UnitPOC" minOccurs="0" nillable="true">
      <xs:simpleType>
      <xs:restriction base="xs:token">
      <xs:minLength value="1"/>
      <xs:maxLength value="30"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="POCNbr" nillable="true" minOccurs="0" smse:type="PhoneNoType" xdb:SQLName="POCNbr"/>
      <xs:element name="POCEmail" nillable="true" minOccurs="0" smse:type="EmailAddressType" xdb:SQLName="POCEmail"/>
      </xs:sequence>
      </xs:complexType>
      </xs:element>

      I can get data from another schema using the same methods. Both have the *<xsd:import namespace="http://samsecommon.xsd/namespace" schemaLocation="samsecommon.xsd"/>* statement. One difference between the two is the other schema doesn't need the prefix for the top element (sam:SamseMonthly) to load. It doesn't make any difference if I make elementFormDefault="qualified" or "unqualified" for this one.

      Thanks!

      Edited by: user4109719 on Jun 29, 2012 11:32 AM

      Edited by: user4109719 on Jun 29, 2012 11:35 AM
        • 1. Re: Not getting data from XML table
          Jason_(A_Non)
          A quick and untested answer. You are missing the namespace parm (3rd parameter of extractValue) and usage of a namespace prefix on the XPath.

          You are looking for something like
          SELECT EXTRACTVALUE(SamseMonthly, '/sam:SamseMonthly/AviationUIC/OrigUnit/UIC', 'xmlns:sam="http://samsemonthly.org/namespace"') as UIC
          FROM samseMonthly ;
          If you are 10.2 or greater, you will find XMLTable easier to use. Here is a quick example, that also shows XMLCast(XQuery()) as well. {thread:id=2408516}
          • 2. Re: Not getting data from XML table
            878240
            That didn't produce data either.

            We're on 11.2.0.2.0. I've been looking at things like XMLCast and XQuery but first I need to figure out why no data is coming out. Are those somehow able to get data when EXTRACTVALUE doesn't?

            Thanks.
            • 3. Re: Not getting data from XML table
              odie_63
              I don't reproduce using the following setup (simplified, since you never give the full picture) :
              begin
                dbms_xmlschema.deleteSchema(schemaURL => 'samsecommon.xsd', delete_option => dbms_xmlschema.DELETE_CASCADE);
              end;
              /
              
              begin
                dbms_xmlschema.registerSchema(
                  schemaURL => 'samsecommon.xsd'
                , schemaDoc => 
               
              '<?xml version="1.0" encoding="utf-8"?>
              <xs:schema elementFormDefault="qualified"
               xmlns:xs="http://www.w3.org/2001/XMLSchema"
               xmlns:xdb="http://xmlns.oracle.com/xdb"
               targetNamespace="http://samsecommon.xsd/namespace"
               xmlns:smse="http://samsecommon.xsd/namespace">
                <xs:simpleType name="UICType">
                  <xs:restriction base="xs:token">
                    <xs:minLength value="1"/>
                    <xs:maxLength value="6"/>
                  </xs:restriction>
                </xs:simpleType>
              </xs:schema>'
               
                , local => true
                , genTypes => true
                , genTables => false
                , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
                ); 
              end;
              /
              
              begin
                dbms_xmlschema.deleteSchema(schemaURL => 'SamseMonthly.xsd', delete_option => dbms_xmlschema.DELETE_CASCADE);
              end;
              /
              
              begin
                dbms_xmlschema.registerSchema(
                  schemaURL => 'SamseMonthly.xsd'
                , schemaDoc => 
               
              '<?xml version="1.0" encoding="UTF-8"?>
              <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
                         elementFormDefault="unqualified" 
                         attributeFormDefault="unqualified"
                         targetNamespace="http://samsemonthly.org/namespace"
                         xmlns:xdb="http://xmlns.oracle.com/xdb"
                         xmlns:smse="http://samsecommon.xsd/namespace">
                <xs:import namespace="http://samsecommon.xsd/namespace" schemaLocation="samsecommon.xsd"/>
                <xs:element name="SamseMonthly" xdb:defaultTable="SAMSEMONTHLY" xdb:SQLName="SamseMonthly">
                  <xs:complexType xdb:SQLType="SamseMonthly_T">
                    <xs:sequence>
                      <xs:element name="AviationUIC" nillable="true" maxOccurs="unbounded" xdb:SQLName="AviationUIC">
                        <xs:complexType xdb:SQLType="AviationUIC_T">
                          <xs:sequence>
                            <xs:element name="OrigUnit" nillable="true" xdb:SQLName="OrigUnit">
                              <xs:complexType xdb:SQLType="OrigUnit_T">
                                <xs:sequence>
                                  <xs:element name="UIC" nillable="true" type="smse:UICType" xdb:SQLName="UIC"></xs:element>
                                  <xs:element name="UnitName" nillable="true" xdb:SQLName="UnitName">
                                    <xs:simpleType>
                                      <xs:restriction base="xs:token">
                                        <xs:minLength value="1"/>
                                        <xs:maxLength value="80"/>
                                      </xs:restriction>
                                    </xs:simpleType>
                                  </xs:element>
                                  <xs:element name="UtilCde" minOccurs="0" nillable="true" xdb:SQLName="UtilCde">
                                    <xs:simpleType>
                                      <xs:restriction base="xs:token">
                                        <xs:length value="1"/>
                                      </xs:restriction>
                                    </xs:simpleType>
                                  </xs:element>
                                  <xs:element name="IsReporter" nillable="true" type="xs:boolean" xdb:SQLName="IsReporter"/>
                                  <xs:element name="UnitPOC" xdb:SQLName="UnitPOC" minOccurs="0" nillable="true">
                                    <xs:simpleType>
                                      <xs:restriction base="xs:token">
                                        <xs:minLength value="1"/>
                                        <xs:maxLength value="30"/>
                                      </xs:restriction>
                                    </xs:simpleType>
                                  </xs:element>
                                </xs:sequence>
                              </xs:complexType>
                            </xs:element>
                          </xs:sequence>
                        </xs:complexType>
                      </xs:element>
                    </xs:sequence>
                  </xs:complexType>
                </xs:element>
              </xs:schema>'
               
                , local => true
                , genTypes => true
                , genTables => true
                , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
                ); 
              end;
              /
              
              
              INSERT INTO SamseMonthly
              VALUES (
               XMLType('<?xml version="1.0" encoding="utf-8"?>
              <sam:SamseMonthly xmlns:sam="http://samsemonthly.org/namespace">
              <AviationUIC>
              <OrigUnit>
              <UIC>ZABCD0</UIC>
              <UnitName>ABC</UnitName>
              <IsReporter>true</IsReporter>
              <UnitPOC>AAAAAAAAAAA</UnitPOC>
              </OrigUnit>
              </AviationUIC>
              </sam:SamseMonthly>')
              );
              Queries :
              SQL> SELECT extractvalue(
                2           object_value
                3         , '/sam:SamseMonthly/AviationUIC/OrigUnit/UIC'
                4         , 'xmlns:sam="http://samsemonthly.org/namespace"'
                5         ) as UIC
                6  FROM SamseMonthly;
               
              UIC
              ------------------------
              ZABCD0
               
              SQL> 
              SQL> SELECT XMLCast(
                2           XMLQuery('declare namespace sam = "http://samsemonthly.org/namespace"; (: :)
                3                     /sam:SamseMonthly/AviationUIC/OrigUnit/UIC'
                4                    passing object_value
                5                    returning content)
                6           as varchar2(6)
                7         ) as UIC
                8  FROM SamseMonthly;
               
              UIC
              ------
              ZABCD0
               
              Any further details you can give to analyze the issue?


              On a side note :
              1) As already pointed out in a previous thread : {message:id=10366978}
              this is not correct :
              smse:type="UICType"
              By using that, you simply don't refer to the type definition from samsecommon.xsd, the default element definition is used instead, and this results in Oracle creating a VARCHAR2(4000) to store the data in place of VARCHAR2(6).
              It should be :
              type="smse:UICType"
              Of course, this also applies to all other types you import from the samsecommon schema.


              2) You don't need to add defaultTable annotations for non globally-defined elements, they'll be ignored :
              xdb:defaultTable="AviationUIC"
              • 4. Re: Not getting data from XML table
                878240
                Thanks I'm able to get data after correcting those annotations using XMLQuery but it still yields nulls using EXTRACTVALUE. Strange.

                Now I can't get it to load after putting the FileInfo element into the instance, which is based on another schema: samsecommon.xsd.

                This is the start of samsecommon.xsd:

                <?xml version="1.0" encoding="utf-8"?>
                <xs:schema elementFormDefault="qualified"
                xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
                xmlns:xs="http://www.w3.org/2001/XMLSchema"
                xmlns:xdb="http://xmlns.oracle.com/xdb"
                xdb:storeVarrayAsTable="true"
                targetNamespace="http://samsecommon.xsd/namespace"
                xmlns:smse="http://samsecommon.xsd/namespace">

                This is the type in the schema that is causing an error when it's in the instance:

                <xs:complexType name="SamseFileInfoType" xdb:SQLType="SamseFileInfoType_T">
                <xs:sequence>
                <xs:element name="OriginaFilelName" type="xs:string"/>
                <xs:element name="Source">
                <xs:complexType xdb:SQLType="SOURCE_T">
                <xs:sequence>
                <xs:element name="Name" type="xs:string"/>
                <xs:element name="Version" type="xs:decimal"/>
                </xs:sequence>
                </xs:complexType>
                </xs:element>
                <xs:element name="Submitter">
                <xs:complexType xdb:SQLType="SUBMITTER_T">
                <xs:sequence>
                <xs:element name="UIC" type="smse:UICType" xdb:SQLName="UIC"/>
                <xs:element name="CRTFMT" type="smse:CRTFMTType" nillable="true" xdb:SQLName="CRTFMT"/>
                <xs:element name="UnitName" xdb:SQLName="UNITNAME">
                <xs:simpleType>
                <xs:restriction base="xs:token">
                <xs:minLength value="1"/>
                <xs:maxLength value="80"/>
                </xs:restriction>
                </xs:simpleType>
                </xs:element>
                <xs:element name="OrgLocCde" type="smse:OrgLocCdeEnum" nillable="true"/>
                <xs:element name="DPICode" type="smse:DPICodeType" nillable="true" xdb:SQLName="DPICODE"/>
                <xs:element name="UnitPOC" nillable="true">
                <xs:simpleType>
                <xs:restriction base="xs:token">
                <xs:minLength value="1"/>
                <xs:maxLength value="30"/>
                </xs:restriction>
                </xs:simpleType>
                </xs:element>
                <xs:element name="IP" type="xs:token"/>
                <xs:element name="POCEmail" type="smse:EmailAddressType" nillable="true" xdb:SQLName="POCEMAIL"/>
                </xs:sequence>
                </xs:complexType>
                </xs:element>
                <xs:element name="CreatedDate" type="xs:dateTime"/>
                <xs:element name="Period">
                <xs:complexType xdb:SQLType="PERIOD_T">
                <xs:sequence>
                <xs:element name="StartDate" type="xs:dateTime"/>
                <xs:element name="EndDate" type="xs:dateTime"/>
                </xs:sequence>
                </xs:complexType>
                </xs:element>
                </xs:sequence>
                </xs:complexType>

                I registered samsecommon as a global schema.

                I added the FileInfo element to the SamseMonthly schema:

                <xs:complexType xdb:SQLType="SamseMonthly_T">
                <xs:sequence>
                *<xs:element name="FileInfo" minOccurs="0" type="smse:SamseFileInfoType"/>*
                <xs:element name="AviationUIC" nillable="true" maxOccurs="unbounded" xdb:SQLName="AviationUIC" xdb:defaultTable="AviationUIC">
                <xs:annotation>
                <xs:documentation>1 element for each UIC being reported (multiple UICs from 2E box)</xs:documentation>
                </xs:annotation>
                <xs:complexType xdb:SQLType="AviationUIC_T">
                <xs:sequence>

                Here's the instance:

                <?xml version="1.0" encoding="utf-8"?>
                <sam:SamseMonthly xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sam="http://samsemonthly.org/namespace">
                *<FileInfo>*
                *<OriginaFilelName>C:\ACFT\SAMSEMONTHLY.XML</OriginaFilelName>*
                *<Source>*
                *<Name>SAMSEMONTHLY</Name>*
                *<Version>1.0</Version>*
                *</Source>*
                *<Submitter>*
                *<UIC>ZZZZZZ</UIC>*
                *<CRTFMT>CRT</CRTFMT>*
                *<UnitName>ABC</UnitName>*
                *<OrgLocCde>A</OrgLocCde>*
                *<DPICode>Need DPICode</DPICode>*
                *<POCNbr>(888)999-0000</POCNbr>*
                *<IP>000.000.0.0</IP>*
                *<POCEmail xsi:nil="true" />*
                *</Submitter>*
                *<CreatedDate>2012-05-10T12:27:11.0936683</CreatedDate>*
                *<Period>*
                *<StartDate>2012-04-10T00:00:00</StartDate>*
                *<EndDate>2012-05-10T00:00:00</EndDate>*
                *</Period>*
                *</FileInfo>*
                <AviationUIC>
                <OrigUnit>
                <UIC>ZABCD0</UIC>
                <UnitName>ABC</UnitName>
                <IsReporter>true</IsReporter>
                <UnitPOC>AAAAAAAAAAA</UnitPOC>
                </OrigUnit>
                </AviationUIC>
                </sam:SamseMonthly>

                INSERT INTO samseMonthly
                VALUES (
                XMLType(
                bfilename('XML_DIR', 'SAMSEMonthly2.xml'),
                NLS_CHARSET_ID('UTF8'))
                )

                XMLType(
                *
                ERROR at line 3:
                ORA-30937: No schema definition for 'FileInfo' (namespace '##local') in parent '/SamseMonthly'

                In the SamseMonthly schema I tried to use *<xs:element name="FileInfo" minOccurs="0" type="smse:SamseFileInfoType_T"/>* but it gave an error:

                ORA-31154: invalid XML document
                ORA-19202: Error occurred in XML processing
                LSX-00020: unknown type "smse:SamseFileInfoType_T"

                The type exists:

                desc "SamseFileInfoType_T"

                "SamseFileInfoType_T" is NOT FINAL
                Name Null? Type
                ----------------------------------------------------------------- -------- ------------------------
                SYS_XDBPD$ XDB.XDB$RAW_LIST_T
                OriginaFilelName VARCHAR2(4000 CHAR)
                Source ULLSAE.SOURCE_T
                Submitter ULLSAE.SUBMITTER_T
                CreatedDate TIMESTAMP(6)
                Period ULLSAE.PERIOD_T

                I also tried making it xdb:SQLType="SamseFileInfoType" for the samsecommon.xsd schema instead of "SamseFileInfoType_T". It allows the SamseMonthly schema to be created using that but won't load the instance.

                I made sure SamseMonthly.xsd schema has elementFormDefault="qualified".
                • 5. Re: Not getting data from XML table
                  odie_63
                  In the SamseMonthly schema I tried to use *<xs:element name="FileInfo" minOccurs="0" type="smse:SamseFileInfoType_T"/>* but it gave an error:

                  ORA-31154: invalid XML document
                  ORA-19202: Error occurred in XML processing
                  LSX-00020: unknown type "smse:SamseFileInfoType_T"
                  Expected behaviour.
                  Do not mistake xml schema type definitions for xdb annotations to control Oracle objects naming. That's two different things.

                  What you used in the first place is correct, keep it like this :
                  <xs:element name="FileInfo" minOccurs="0" type="smse:SamseFileInfoType"/>
                  and in samsecommon.xsd :
                  <xs:complexType name="SamseFileInfoType" xdb:SQLType="SamseFileInfoType_T">
                  ...
                  I made sure SamseMonthly.xsd schema has elementFormDefault="qualified".
                  That's the problem, I think.

                  In the first post you said you were using elementFormDefault="unqualified", that's why you had to namespace-qualify only the root node (because it is defined globally in the main schema).
                  Now, if you're switching back to "qualified", that means all elements must be in the target namespace (we've been through this before in a previous thread).
                  For example, with a default namespace :
                  <?xml version="1.0" encoding="utf-8"?>
                  <SamseMonthly xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                                xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                                xmlns="http://samsemonthly.org/namespace">
                   <FileInfo>
                    <OriginaFilelName>C:\ACFT\SAMSEMONTHLY.XML</OriginaFilelName>
                    <Source>
                     <Name>SAMSEMONTHLY</Name>
                     <Version>1.0</Version>
                    </Source>
                    <Submitter>
                     <UIC>ZZZZZZ</UIC>
                     <CRTFMT>CRT</CRTFMT>
                     <UnitName>ABC</UnitName>
                     <OrgLocCde>A</OrgLocCde>
                     <DPICode>Need DPICode</DPICode>
                     <POCNbr>(888)999-0000</POCNbr>
                     <IP>000.000.0.0</IP>
                     <POCEmail xsi:nil="true" />
                    </Submitter>
                    <CreatedDate>2012-05-10T12:27:11.0936683</CreatedDate>
                    <Period>
                     <StartDate>2012-04-10T00:00:00</StartDate>
                     <EndDate>2012-05-10T00:00:00</EndDate>
                    </Period>
                   </FileInfo>
                   <AviationUIC>
                    <OrigUnit>
                     <UIC>ZABCD0</UIC>
                     <UnitName>ABC</UnitName>
                     <IsReporter>true</IsReporter>
                     <UnitPOC>AAAAAAAAAAA</UnitPOC>
                    </OrigUnit>
                   </AviationUIC>
                  </SamseMonthly>
                  • 6. Re: Not getting data from XML table
                    878240
                    I was able to get it to load after I saw there were errors in annotations in the samsecommon schema.

                    I loaded an instance with multiple occurrences of the Equipment element. This is the first 3 occurences:

                    <?xml version="1.0" encoding="utf-8"?>
                    <sam:SamseMonthly xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sam="http://samsemonthly.org/namespace">
                    <AviationUIC>
                    <OrigUnit>
                    <UIC>WV7PB0</UIC>
                    <UnitName>abcd</UnitName>
                    <OrgLocCde>A</OrgLocCde>
                    <DPICode>Need DPICode</DPICode>
                    <IsReporter>true</IsReporter>
                    <UnitPOC />
                    <POCNbr>(999)999-0000</POCNbr>
                    </OrigUnit>
                    <Equipment>
                    <INC>A</INC>
                    <FSCCage />
                    <NIINPNO />
                    <WorkUnitCde>76F01E</WorkUnitCde>
                    <EIC>***</EIC>
                    <SerialNbr>UNK01294C</SerialNbr>
                    <EquipData>
                    <Model>OH-58D</Model>
                    <Nomenclature>LOW SPEED MOTOR</Nomenclature>
                    </EquipData>
                    </Equipment>
                    <Equipment>
                    <INC>A</INC>
                    <FSCCage />
                    <NIINPNO />
                    <WorkUnitCde>76F01</WorkUnitCde>
                    <EIC>***</EIC>
                    <SerialNbr>03671C</SerialNbr>
                    <EquipData>
                    <Model>OH-58D</Model>
                    <Nomenclature>TRANSMITTER, COUNTERMEASU</Nomenclature>
                    </EquipData>
                    </Equipment>
                    <Equipment>
                    <INC>A</INC>
                    <FSCCage />
                    <NIINPNO />
                    <WorkUnitCde>11D01D</WorkUnitCde>
                    <EIC>***</EIC>
                    <SerialNbr>HRFS1344</SerialNbr>
                    <EquipData>
                    <Model>OH-58D</Model>
                    <Nomenclature>SERVOACTUATOR CYCLIC</Nomenclature>
                    </EquipData>
                    </Equipment>

                    When I select the SerialNbr it concatenates them all together:

                    SELECT XMLCast(
                    XMLQuery('declare namespace sam = "http://samsemonthly.org/namespace"; (: :)
                    /sam:SamseMonthly/AviationUIC/Equipment/SerialNbr'
                    passing OBJECT_VALUE
                    returning content)
                    as varchar2(25)
                    ) as SerialNbr
                    FROM samsemonthly;


                    SERIALNBR
                    -------------------------
                    UNK01294C03671CHRFS134403

                    These methods still return nulls.

                    SELECT extract(OBJECT_VALUE, '/SamseMonthly/AviationUIC/OrigUnit/UIC') as UIC
                    FROM samsemonthly;

                    SELECT EXTRACTVALUE(OBJECT_VALUE,
                    '/SamseMonthly/AviationUIC/OrigUnit/UIC') as UIC
                    FROM samsemonthly;

                    Thanks for your valuable assistance.
                    • 7. Re: Not getting data from XML table
                      odie_63
                      When I select the SerialNbr it concatenates them all together:
                      Yes, expected behaviour.

                      XMLQuery extracts a sequence of nodes, but the result is always an XMLType fragment, it does not put items in separate rows :
                      <SerialNbr>UNK01294C</SerialNbr><SerialNbr>03671C</SerialNbr><SerialNbr>HRFS1344</SerialNbr>
                      When you apply XMLCast, the content is cast to the specified SQL scalar datatype, in this case by putting all element values together.

                      If you want items in separate rows, then there's XMLTable :
                      SELECT x.SerialNbr
                      FROM samsemonthly t
                         , XMLTable(
                             XMLNamespaces('http://samsemonthly.org/namespace' as "sam")
                           , '/sam:SamseMonthly/AviationUIC/Equipment'
                             passing t.object_value
                             columns SerialNbr varchar2(25) path 'SerialNbr'
                           ) x
                      ;
                      Items returned by the main XQuery expression <tt>'/sam:SamseMonthly/AviationUIC/Equipment'</tt> are output in their own relational row.
                      Then, if necessary, the COLUMNS clause projects part of the row element into separate relational columns (here SerialNbr).

                      These methods still return nulls.

                      SELECT extract(OBJECT_VALUE, '/SamseMonthly/AviationUIC/OrigUnit/UIC') as UIC
                      FROM samsemonthly;

                      SELECT EXTRACTVALUE(OBJECT_VALUE,
                      '/SamseMonthly/AviationUIC/OrigUnit/UIC') as UIC
                      FROM samsemonthly;
                      Don't use them :)

                      OK, they're deprecated but should still work regardless so it would be interesting to know why they doesn't return anything in your case.
                      Rigorously, one should use the namespace mapping but these functions are known to work without specifying it if the table is schema-based.
                      Personally, I had never relied on that behaviour. It's good practice to always give the namespace mapping.

                      Could you try again the example with namespace that A_Non and I gave earlier?

                      As said earlier too, EXTRACT and EXTRACTVALUE works for me, but again I don't have the same test case.
                      To get to the bottom of this (for glory ;) ) I'd need to see your actual samsecommon and samsemonthly schemas, not bits of them.
                      • 8. Re: Not getting data from XML table
                        878240
                        That's working. Thanks for your help.