6 Replies Latest reply: Feb 25, 2013 1:28 AM by Mark Beelen RSS

    retrieve xsi:type attribute with XMLType Object Relational storage"

    Mark Beelen
      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
          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
            "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
              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
                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
                  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
                    Indeed I needed to remove the xsi namespace, thanks!