This discussion is archived
6 Replies Latest reply: Aug 29, 2012 5:00 AM by EricSchrauth RSS

Question regarding xsd and shredding

EricSchrauth Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    EricSchrauth Newbie
    Currently Being Moderated
    Thanks! That did the trick!
  • 3. Re: Question regarding xsd and shredding
    EricSchrauth Newbie
    Currently Being Moderated
    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
    EricSchrauth Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    EricSchrauth Newbie
    Currently Being Moderated
    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.

Legend

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