2 Replies Latest reply: Sep 21, 2012 3:02 AM by 504561 RSS

    Object-Relational Storage - Querying via XPath and directly

    504561
      Oracle 11gr2 on Windows XP
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE     11.2.0.1.0     Production
      TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      Hi,
      Is it possible to query "out-of-line storage" using direct access to object attributtes within the SQL statement ? Please see my test case ...

      Thanks
      --
      --EXEC DBMS_XMLSCHEMA.deleteSchema(SCHEMAURL =>'shiporder_xdb.xsd',DELETE_OPTION => DBMS_XMLSCHEMA.DELETE_CASCADE_FORCE);
      
      -- schema registration - xdb annotations 
      EXEC DBMS_XMLSCHEMA.registerSchema(SCHEMAURL =>'shiporder_xdb.xsd',SCHEMADOC => '<?xml version="1.0" encoding="ISO-8859-1" ?><xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" ><xs:element name="shiporder" xdb:defaultTable="SHIPORDERS_TBL"><xs:complexType xdb:SQLType="SHIPORDER_TYP"><xs:sequence><xs:element name="orderperson" type="xs:string" xdb:SQLName="ORDER_PERSON"/><xs:element name="shipto" xdb:SQLName="SHIP_TO"><xs:complexType xdb:SQLType="SHIPTO_TYP"><xs:sequence><xs:element name="name" type="xs:string" xdb:SQLName="NAME"/><xs:element name="address" type="xs:string" xdb:SQLName="ADDRESS"/><xs:element name="city" type="xs:string" xdb:SQLName="CITY"/><xs:element name="country" type="xs:string" xdb:SQLName="COUNTRY"/></xs:sequence></xs:complexType></xs:element><xs:element name="item" maxOccurs="unbounded" xdb:SQLInline="false" xdb:defaultTable="ITEMS_TBL" xdb:SQLName="ITEM"><xs:complexType xdb:SQLType="ITEM_TYP"><xs:sequence><xs:element name="title" type="xs:string" xdb:SQLName="TITLE"/><xs:element name="note" type="xs:string" minOccurs="0" xdb:SQLName="NOTE"/><xs:element name="quantity" type="xs:positiveInteger" xdb:SQLName="QUANTITY"/><xs:element name="price" type="xs:decimal" xdb:SQLName="PRICE"/></xs:sequence></xs:complexType></xs:element></xs:sequence><xs:attribute name="orderid" type="xs:string" use="required" xdb:SQLName="ORDER_ID"/></xs:complexType></xs:element></xs:schema>',LOCAL => TRUE,GENTYPES => TRUE,GENTABLES => TRUE);
      
      --insert data
      INSERT INTO SHIPORDERS_TBL
           VALUES (
                     XMLType ('<?xml version="1.0" encoding="ISO-8859-1"?>
      <shiporder orderid="889923" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="shiporder_xdb.xsd">
        <orderperson>John Smith</orderperson>
        <shipto>
          <name>Ola Nordmann</name>
          <address>Langgt 23</address>
          <city>4000 Stavanger</city>
          <country>Norway</country>
        </shipto>
        <item>
          <title>Empire Burlesque</title>
          <note>Special Edition</note>
          <quantity>1</quantity>
          <price>10.90</price>
        </item>
        <item>
          <title>Hide your heart</title>
          <quantity>1</quantity>
          <price>9.90</price>
        </item>
      </shiporder>'));
                              
      INSERT INTO SHIPORDERS_TBL
           VALUES (
                     XMLType ('<?xml version="1.0" encoding="ISO-8859-1"?>
      <shiporder orderid="889923"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:noNamespaceSchemaLocation="shiporder_xdb.xsd">
        <orderperson>George Hull</orderperson>
        <shipto>
          <name>Ol Nord</name>
          <address>Lan 2</address>
          <city>5000 Stav</city>
          <country>Finland</country>
        </shipto>
        <item>
          <title>Burlesque</title>
          <note>Nothing</note>
          <quantity>1</quantity>
          <price>20.90</price>
        </item>
        <item>
          <title>Show your heart</title>
          <quantity>2</quantity>
          <price>1.90</price>
        </item>
      </shiporder> '));
      
      COMMIT;
                              
       
       -- using XMLTable = OK
       SELECT d.order_person,d.shipto_name,d2.title
       FROM SHIPORDERS_TBL o,
        XMLTable( '/shiporder' PASSING OBJECT_VALUE
        COLUMNS order_person VARCHAR2(4000) PATH 'orderperson',
                        shipto_name VARCHAR2(4000) PATH 'shipto/name') as d,
        XMLTable('/shiporder/item' PASSING OBJECT_VALUE
        COLUMNS title VARCHAR2(4000) PATH 'title'  ) d2
        
        -- direct access ??    
        SELECT s."XMLDATA"."ORDER_PERSON" order_person,
             s."XMLDATA"."SHIP_TO"."NAME" shipto_name       
        FROM SHIPORDERS_TBL s;
        
        SELECT s."XMLDATA"."TITLE" TITLE,
               s."XMLDATA"."PRICE" PRICE       
        FROM ITEMS_TBL s;
                                                    
       -- how to join tables ??   
       SELECT s."XMLDATA"."ORDER_PERSON" order_person,
             s."XMLDATA"."SHIP_TO"."NAME" shipto_name,
             i."XMLDATA"."TITLE"
        FROM SHIPORDERS_TBL s, ITEMS_TBL i
       WHERE s."XMLDATA"."ITEM" = i. --<< JOIN using REF??
        • 1. Re: Object-Relational Storage - Querying via XPath and directly
          odie_63
          Hi,
          Is it possible to query "out-of-line storage" using direct access to object attributtes within the SQL statement ?
          Yes, it is possible but don't do it.
          It's not a way supported by Oracle. The underlying implementation must stay transparent for the end-user and one must always use the XML abstraction layer to query the data (via XMLTable, XMLQuery and XMLExists).

          That being said, and for the record (only), here's how you can do it :
          SELECT s."XMLDATA"."ORDER_PERSON"   as order_person
               , s."XMLDATA"."SHIP_TO"."NAME" as shipto_name
               , i."XMLDATA"."TITLE"          as title
          FROM SHIPORDERS_TBL s
             , TABLE(s."XMLDATA"."ITEM") t
             , ITEMS_TBL i
          WHERE value(t) = ref(i) ;
          BTW, regarding your first query with XMLTable, I would write it more like this :
          SELECT d.order_person,d.shipto_name,d2.title
          FROM SHIPORDERS_TBL o
             , XMLTable(
                 '/shiporder' 
                 PASSING OBJECT_VALUE
                 COLUMNS order_person VARCHAR2(4000) PATH 'orderperson'
                       , shipto_name  VARCHAR2(4000) PATH 'shipto/name'
                       , items        XMLTYPE        PATH 'item'
               ) as d
             , XMLTable(
                 '/item' 
                 PASSING d.items
                 COLUMNS title        VARCHAR2(4000) PATH 'title'
               ) d2
          ;
          Edited by: odie_63 on 20 sept. 2012 17:10
          • 2. Re: Object-Relational Storage - Querying via XPath and directly
            504561
            I see ... the intermediate nested-table with the ref ... ok, I'am going to use the functions.

            Thanks a lot for your help.