This discussion is archived
6 Replies Latest reply: Feb 24, 2013 11:28 PM by Mark Beelen RSS

retrieve xsi:type attribute with XMLType Object Relational storage"

Mark Beelen Newbie
Currently Being Moderated
Hi,


How to retrieve the xsi:type attribute when using Object Relational storage XMLType.

When you for instance have a ProductOrder object type and a MoveOrder object type which extents from a CustomerOrder type how to retrieve if the CustomerOrder is from attribute type MoveOrder or ProductOrder:

<ns2:customerOrder xsi:type="ns3:UpcProductOrder">


I would like to use it in the XMLTable function in combination with XPath Rewrite.

database version 11.2.0.2
  • 1. Re: retrieve xsi:type attribute with XMLType Object Relational storage"
    odie_63 Guru
    Currently Being Moderated
    Hi,

    You can query it directly with an attribute axis "ns2:customerOrder/@xsi:type". You don't have to declare the xsi prefix, it's predefined in Oracle's XQuery implementation.

    If you want to make tests, see also the <tt>instance of</tt> operator, and <tt>typeswitch</tt> statement.

    Here's an example with <tt>typeswitch</tt> : {message:id=10050106}
  • 2. Re: retrieve xsi:type attribute with XMLType Object Relational storage"
    Mark Beelen Newbie
    Currently Being Moderated
    "ns2:c"ns2:customerOrder/@xsi:type" doesn't work when I use object relation storage:

    ORA-19276: XPST0005 - XPath step specifies an invalid element/attribute name: (type)
    19276. 00000 - "XP0005 - XPath step specifies an invalid element/attribute name: (%s)"


    When I only use binary xml without the object relational storage it indeed works as expected.
  • 3. Re: retrieve xsi:type attribute with XMLType Object Relational storage"
    odie_63 Guru
    Currently Being Moderated
    Mark Beelen wrote:
    "ns2:c"ns2:customerOrder/@xsi:type" doesn't work when I use object relation storage:
    Give us a test case and we'll see what's wrong, thanks.
  • 4. Re: retrieve xsi:type attribute with XMLType Object Relational storage"
    Mark Beelen Newbie
    Currently Being Moderated
    Hi,

    Hereby a testcase. First part with object relational storage, second part without.

    --
    -- XSD
    <?xml version="1.0" encoding="utf-8"?>
    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://bla.com"
    xmlns:ns1="http://bla.com" targetNamespace="http://bla.com"
    elementFormDefault="qualified" attributeFormDefault="unqualified">
    <xs:element name="customerOrder" type="CustomerOrder"/>
    <xs:complexType name="CustomerOrder" abstract="false">
    <xs:sequence>
    <xs:element name="orderId" type="xs:string" minOccurs="0"/>
    </xs:sequence>
    </xs:complexType>
    <xs:complexType name="MoveOrder" abstract="false">
    <xs:complexContent>
    <xs:extension base="CustomerOrder">
    <xs:sequence>
    <xs:element name="newAddress" type="xs:string" minOccurs="0"/>
    </xs:sequence>
    </xs:extension>
    </xs:complexContent>
    </xs:complexType>
    <xs:complexType name="ProductOrder" abstract="false">
    <xs:complexContent>
    <xs:extension base="CustomerOrder">
    <xs:sequence>
    <xs:element name="orderItem" type="xs:string" minOccurs="0"/>
    </xs:sequence>
    </xs:extension>
    </xs:complexContent>
    </xs:complexType>
    </xs:schema>


    --
    -- register schema object relation storage
    BEGIN
    DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'test_otn.xsd',
    SCHEMADOC => bfilename('XMLDIR','test_otn.xsd'),
    CSID => nls_charset_id('AL32UTF8'));
    END;
    /

    -- insert record into database
    -- actual table name will be different
    insert into "customerOrder20675_TAB" values (xmltype
    ('<?xml version="1.0" encoding="UTF-8" ?>
    <ns1:customerOrder xmlns:ns1="http://bla.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns1:ProductOrder">
    <ns1:orderId>a</ns1:orderId>
    <ns1:orderItem>b</ns1:orderItem>
    </ns1:customerOrder>
    '));

    SELECT order_id
    ,order_item
         ,order_type
    FROM "customerOrder20675_TAB"
    ,XMLTABLE ( xmlnamespaces ('http://bla.com' as "ns1", 'http://www.w3.org/2001/XMLSchema-instance' as "xsi")
    ,'for $i in /ns1:customerOrder
    return $i'
    passing object_value
    columns order_id varchar2(20) path '/ns1:customerOrder/ns1:orderId'
    ,order_item varchar2(20) path '/ns1:customerOrder/ns1:orderItem'
         ,order_type varchar2(20) path '/ns1:customerOrder/@xsi:type' );

    --          
    --result          
    ERROR at line 1:
    ORA-19276: XPST0005 - XPath step specifies an invalid element/attribute name: (type)


    --
    --          
    -- register schema binary XML without object relational     
    BEGIN
    DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'test_otn.xsd',
    SCHEMADOC => bfilename('XMLDIR','test_otn.xsd'),
         GENTYPES => false,
         GENTABLES => false,
         local => true,
    CSID => nls_charset_id('AL32UTF8'),
         OPTIONS => DBMS_XMLSCHEMA.REGISTER_BINARYXML);
    END;
    /

    --
    -- manually create table
    CREATE TABLE customerOrder OF XMLType XMLType STORE AS SECUREFILE BINARY XML XMLSCHEMA "test_otn.xsd" ELEMENT "customerOrder";          

    -- insert record into database
    -- actual table name will be different
    insert into customerOrder values (xmltype
    ('<?xml version="1.0" encoding="UTF-8" ?>
    <ns1:customerOrder xmlns:ns1="http://bla.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns1:ProductOrder">
    <ns1:orderId>a</ns1:orderId>
    <ns1:orderItem>b</ns1:orderItem>
    </ns1:customerOrder>
    '));

    SELECT order_id
    ,order_item
         ,order_type
    FROM customerOrder
    ,XMLTABLE ( xmlnamespaces ('http://bla.com' as "ns1", 'http://www.w3.org/2001/XMLSchema-instance' as "xsi")
    ,'for $i in /ns1:customerOrder
    return $i'
    passing object_value
    columns order_id varchar2(20) path '/ns1:customerOrder/ns1:orderId'
    ,order_item varchar2(20) path '/ns1:customerOrder/ns1:orderItem'
              ,order_type varchar2(20) path '/ns1:customerOrder/@xsi:type');

    --
    --result
    ORDER_ID ORDER_ITEM ORDER_TYPE
    -------------------- -------------------- --------------------
    a b ProductOrder

    1 row selected.

    Edited by: Mark Beelen on 14-feb-2013 7:27

    Edited by: Mark Beelen on 14-feb-2013 7:28
  • 5. Re: retrieve xsi:type attribute with XMLType Object Relational storage"
    odie_63 Guru
    Currently Being Moderated
    Thanks for the details.

    As said in my first reply, the xsi prefix is predefined, do not try to redeclare it.

    I know that sounds strange and contradicts what the manual says but it seems to work this way with OR storage.

    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#CBAJBDFA
    You can use these prefixes in XQuery expressions without first declaring them in the XQuery-expression prolog. You can redefine any of them except xml in the prolog.
    So documentation bug or implementation bug? I don't know.

    Anyway :
    SQL> BEGIN
      2    DBMS_XMLSCHEMA.registerSchema(
      3      SCHEMAURL => 'test_otn.xsd',
      4      SCHEMADOC => bfilename('TEST_DIR','test_otn.xsd'),
      5      CSID => nls_charset_id('AL32UTF8')
      6    );
      7  END;
      8  /
     
    PL/SQL procedure successfully completed
     
    SQL> 
    SQL> insert into cust_order values (xmltype
      2  ('<?xml version="1.0" encoding="UTF-8" ?>
      3  <ns1:customerOrder xmlns:ns1="http://bla.com"
      4   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      5   xsi:type="ns1:ProductOrder">
      6  <ns1:orderId>a</ns1:orderId>
      7  <ns1:orderItem>b</ns1:orderItem>
      8  </ns1:customerOrder>'));
     
    1 row inserted
     
    SQL> 
    SQL> SELECT order_id
      2       , order_item
      3       , order_type
      4  FROM cust_order
      5     , XMLTABLE (
      6         XMLNamespaces ('http://bla.com' as "ns1")
      7       , '/ns1:customerOrder'
      8         passing object_value
      9         columns order_id   varchar2(20) path 'ns1:orderId'
     10               , order_item varchar2(20) path 'ns1:orderItem'
     11               , order_type varchar2(30) path '@xsi:type'
     12       ) ;
     
    ORDER_ID             ORDER_ITEM           ORDER_TYPE
    -------------------- -------------------- ------------------------------
    a                    b                    http://bla.com:ProductOrder
     
    And if you want only the local-name :
    SQL> SELECT order_id
      2       , order_item
      3       , order_type
      4  FROM cust_order
      5     , XMLTABLE (
      6         XMLNamespaces ('http://bla.com' as "ns1")
      7       , '/ns1:customerOrder'
      8         passing object_value
      9         columns order_id   varchar2(20) path 'ns1:orderId'
     10               , order_item varchar2(20) path 'ns1:orderItem'
     11               , order_type varchar2(30) path 'local-name-from-QName(@xsi:type)'
     12       ) ;
     
    ORDER_ID             ORDER_ITEM           ORDER_TYPE
    -------------------- -------------------- ------------------------------
    a                    b                    ProductOrder
     
    Hope that helps.
  • 6. Re: retrieve xsi:type attribute with XMLType Object Relational storage"
    Mark Beelen Newbie
    Currently Being Moderated
    Indeed I needed to remove the xsi namespace, thanks!

Legend

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