6 Replies Latest reply: Aug 29, 2012 7:00 AM by Eric Schrauth RSS

    Question regarding xsd and shredding

    Eric Schrauth
      All,

      I have the following xsd and xml file, I register the xsd in xmldb and create a resource from the xml file. Upon registration of the xsd, the proper (I think) tables are created and upon creating the resource the primary table seems to have a row in it. I am having a problem trying to figure out how to get the values out of the types that have the enumeration values. Any help would be appreciated.

      $sqlplus / as sysdba

      SQL*Plus: Release 10.2.0.3.0 - Production on Tue Aug 28 10:40:41 2012

      Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
      With the Partitioning, Real Application Clusters, OLAP and Data Mining options

      SQL> select * from V$VERSION
      2 /

      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
      PL/SQL Release 10.2.0.3.0 - Production
      CORE 10.2.0.3.0 Production
      TNS for HPUX: Version 10.2.0.3.0 - Production
      NLSRTL Version 10.2.0.3.0 - Production

      SQL>


      xsd:
      <?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="ITEMS" xdb:defaultTable="TMP_ITEMS">
                <xs:complexType xdb:SQLType="TMP_ITEMS_T" xdb:maintainDOM="false">
                     <xs:sequence>
                          <xs:element name="ITEM_GROUP" xdb:SQLCollType="ITEM_GROUP_ROW_NTT">
                               <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                         <xs:enumeration value="GROUP_A"/>
                                         <xs:enumeration value="GROUP_B"/>
                                         <xs:enumeration value="GROUP_C"/>
                                         <xs:enumeration value="GROUP_D"/>
                                    </xs:restriction>
                               </xs:simpleType>
                          </xs:element>
                          <xs:element ref="ITEM" maxOccurs="unbounded"/>
                     </xs:sequence>
                </xs:complexType>
           </xs:element>
           <xs:element name="ITEM" xdb:defaultTable="TMP_ITEM" xdb:SQLType="TMP_ITEM_T" xdb:maintainDOM="false">
                <xs:complexType>
                     <xs:sequence>
                          <xs:element name="ITEM_NUMBER" type="xs:string"/>
                          <xs:element name="ITEM_STATUS">
                               <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                         <xs:enumeration value="ACTIVE"/>
                                         <xs:enumeration value="INACTIVE"/>
                                    </xs:restriction>
                               </xs:simpleType>
                          </xs:element>
                          <xs:element name="ITEM_DATE" type="xs:string"/>
                          <xs:element name="ITEM_ID" type="xs:string"/>
                          <xs:element ref="ITEM_SUBGROUP_1" maxOccurs="unbounded"/>               
                          <xs:element ref="ITEM_SUBGROUP_2" maxOccurs="unbounded"/>               
                     </xs:sequence>
                </xs:complexType>
           </xs:element>
           <xs:element name="ITEM_SUBGROUP_1" xdb:defaultTable="TMP_ITEM_SUBGROUP_1" xdb:SQLType="TMP_ITEM_SUBGROUP_1_T" xdb:maintainDOM="false">
                <xs:complexType>
                     <xs:sequence>
                          <xs:element name="SUBGROUP_1_ID" type="xs:string"/>
                     </xs:sequence>
                </xs:complexType>
           </xs:element>
           <xs:element name="ITEM_SUBGROUP_2" xdb:defaultTable="TMP_ITEM_SUBGROUP_2" xdb:SQLType="TMP_ITEM_SUBGROUP_2_T" xdb:maintainDOM="false">
                <xs:complexType>
                     <xs:sequence>
                          <xs:element name="SUBGROUP_2_ID" type="xs:string"/>
                          <xs:element name="SUBGROUP_2_TYPE">
                               <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                         <xs:enumeration value="SUBGROUP_TYPE_2_TYPE_A"/>
                                         <xs:enumeration value="SUBGROUP_TYPE_2_TYPE_B"/>
                                         <xs:enumeration value="SUBGROUP_TYPE_2_TYPE_C"/>
                                    </xs:restriction>
                               </xs:simpleType>
                          </xs:element>
                     </xs:sequence>
                </xs:complexType>
           </xs:element>
      </xs:schema>
      xml:
      <?xml version="1.0"?>
      <ITEMS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="tmp_items.xsd">
           <ITEM_GROUP>GROUP_A</ITEM_GROUP>
           <ITEM>
                <ITEM_NUMBER>1</ITEM_NUMBER>
                <ITEM_STATUS>ACTIVE</ITEM_STATUS>
                <ITEM_ID>0000000123</ITEM_ID>
                <ITEM_SUBGROUP_1>
                     <SUBGROUP_1_ID>SG1_000000987</SUBGROUP_1_ID>
                </ITEM_SUBGROUP_1>
                <ITEM_SUBGROUP_2>
                     <SUBGROUP_2_ID>SG2_000000987</SUBGROUP_2_ID>
                     <SUBGROUP_2_TYPE>SUBGROUP_TYPE_2_TYPE_A</SUBGROUP_2_TYPE>
                </ITEM_SUBGROUP_2>
           </ITEM>
           <ITEM>
                <ITEM_NUMBER>2</ITEM_NUMBER>
                <ITEM_STATUS>INACTIVE</ITEM_STATUS>
                <ITEM_ID>0000000456</ITEM_ID>
                <ITEM_SUBGROUP_1>
                     <SUBGROUP_1_ID>SG1_000000654</SUBGROUP_1_ID>
                </ITEM_SUBGROUP_1>
                <ITEM_SUBGROUP_2>
                     <SUBGROUP_2_ID>SG2_000000654</SUBGROUP_2_ID>
                     <SUBGROUP_2_TYPE>SUBGROUP_TYPE_2_TYPE_A</SUBGROUP_2_TYPE>
                </ITEM_SUBGROUP_2>
           </ITEM>
      </ITEMS>
      registration of xsd:
      BEGIN
        IF DBMS_XDB.CREATERESOURCE(
           abspath => '/public/tmp/tmp_items.xsd',
           data    => BFILENAME ('XSD_DIR','tmp_items.xsd')
           )
        THEN
           DBMS_XMLSCHEMA.REGISTERSCHEMA(
              schemaurl => 'tmp_items.xsd',
              schemadoc => sys.UriFactory.getUri('/public/tmp/tmp_items.xsd')
              );
           COMMIT;
        END IF;
      END;
      creation on xml resource:
      declare
         v_return BOOLEAN;
      BEGIN
         v_return := DBMS_XDB.CREATERESOURCE(abspath => '/public/tmp/tmp_items.xml', data => BFILENAME('XML_DIR', 'tmp_items.xml'));
      END;
      query the high level:
        select xml.item_number                  as item_number,
               extractvalue(value(i),'/ITEMS/ITEM_GROUP') item_status
        from tmp_items i,
             table(i.xmldata.item) xml
      query the sub_group for one high level:
      select *
      from table(
      select xml.ITEM_SUBGROUP_2
        from tmp_items i,
             table(i.xmldata.item) xml
             where xml.item_number = '1')
      When I execute the above query, I can't seem to extract the value out of the subgroup_2_type element. I used the extractvalue function to get it out of the high level, but can't seem to get it right here.

      Thanks

      Eric

      Edited by: Eric Schrauth on Aug 28, 2012 8:39 AM
        • 1. Re: Question regarding xsd and shredding
          odie_63
          Hi Eric,

          Accessing the data via the XMLDATA pseudocolumn is not supported by Oracle, except in DDL if you want to create indexes on the relational structure.

          So, don't use it in regular SQL queries.

          This is the recommended method :
          SQL> SELECT x1.item_number
            2       , x1.item_status
            3       , x2.*
            4  FROM tmp_items t
            5     , XMLTable(
            6         '/ITEMS/ITEM'
            7         passing t.object_value
            8         columns item_number     varchar2(15) path 'ITEM_NUMBER'
            9               , item_status     varchar2(15) path 'ITEM_STATUS'
           10               , item_subgroup_2 xmltype      path 'ITEM_SUBGROUP_2'
           11       ) x1
           12     , XMLTable(
           13         '/ITEM_SUBGROUP_2'
           14         passing x1.item_subgroup_2
           15         columns subgroup_2_id   varchar2(15) path 'SUBGROUP_2_ID'
           16               , subgroup_2_type varchar2(15) path 'SUBGROUP_2_TYPE'
           17       ) x2
           18  WHERE x1.item_number = '1'
           19  ;
           
          ITEM_NUMBER     ITEM_STATUS     SUBGROUP_2_ID   SUBGROUP_2_TYPE
          --------------- --------------- --------------- ---------------
          1               ACTIVE          SG2_000000987   SUBGROUP_TYPE_2
           
          • 2. Re: Question regarding xsd and shredding
            Eric Schrauth
            Thanks! That did the trick!
            • 3. Re: Question regarding xsd and shredding
              Eric Schrauth
              Another problem. What if the ITEM_SUBGROUP_1 or ITEM_SUBGROUP_2 are not present. It seems when they are missing, it is causing no rows to be returned.
              • 4. Re: Question regarding xsd and shredding
                Eric Schrauth
                Think I figured it out. Have to create a base view on the elements that will always be there and other views on the items that might not and query them appropriately. Right?
                • 5. Re: Question regarding xsd and shredding
                  odie_63
                  Have to create a base view on the elements that will always be there and other views on the items that might not and query them appropriately. Right?
                  Not necessarily.

                  The usual approach is to use an OUTER JOIN :
                  SELECT x1.item_number
                       , x1.item_status
                       , x2.*
                  FROM tmp_items t
                     , XMLTable(
                         '/ITEMS/ITEM'
                         passing t.object_value
                         columns item_number     varchar2(15) path 'ITEM_NUMBER'
                               , item_status     varchar2(15) path 'ITEM_STATUS'
                               , item_subgroup_2 xmltype      path 'ITEM_SUBGROUP_2'
                       ) x1
                     , XMLTable(
                         '/ITEM_SUBGROUP_2'
                         passing x1.item_subgroup_2
                         columns subgroup_2_id   varchar2(15) path 'SUBGROUP_2_ID'
                               , subgroup_2_type varchar2(15) path 'SUBGROUP_2_TYPE'
                       ) (+) x2
                  ;
                  Or the equivalent with ANSI JOIN :
                  SELECT x1.item_number
                       , x1.item_status
                       , x2.*
                  FROM tmp_items t
                     , XMLTable(
                         '/ITEMS/ITEM'
                         passing t.object_value
                         columns item_number     varchar2(15) path 'ITEM_NUMBER'
                               , item_status     varchar2(15) path 'ITEM_STATUS'
                               , item_subgroup_2 xmltype      path 'ITEM_SUBGROUP_2'
                       ) x1
                     LEFT OUTER JOIN
                       XMLTable(
                         '/ITEM_SUBGROUP_2'
                         passing x1.item_subgroup_2
                         columns subgroup_2_id   varchar2(15) path 'SUBGROUP_2_ID'
                               , subgroup_2_type varchar2(15) path 'SUBGROUP_2_TYPE'
                       ) x2
                       ON 1 = 1
                  ;
                  • 6. Re: Question regarding xsd and shredding
                    Eric Schrauth
                    Thanks! I was doing the same thing by creating three views and outer joining them into a fourth view. You method is much easier! I didn't know of the syntax that you used. Thanks again for all of your help.