1 Reply Latest reply on Jul 10, 2012 2:22 PM by odie_63

    XML document parsing-- returns no data

      Database 10g R2
      <?xml version="1.0" encoding="UTF-8" ?>
      select x1.messageType,x1.requestId
               , x2.productName,x1.produc
          from zz t
             , xmltable(
                 passing t.zz
                  messageType varchar2(50)         path 'identification/messageType'
                 ,requestId varchar2(30)         path 'identification/requestId'
                 ,produc xmltype   path 'product'
              )  x1
            , xmltable(
                passing x1.produc
                productName  varchar2(40) path 'product/productName'
              )(+) x2
      Nothing is returning from the repeated group. I think that the path is missing, how do I check that I have given correct path
      And I am just trying to simply select as not to use oracle collections(object types)
      Any help would be greatly appreciated.

        • 1. Re: XML document parsing-- returns no data
          The path used in the PATH clause is relative to the context item passed from the sequence returned by the main XQuery expression.
          That means this : <tt>'product/productName'</tt> is actually evaluated as <tt>'/product/product/productName'</tt>, which obviously points to nothing.

          What you need is :
          PATH '/product/productName'
          (note the initial slash meaning "starts at the root node", in this case <tt>product</tt>)

          or, the equivalent, shorter version (I always use this one) :
          PATH 'productName'
          Full query :
          SQL> select x1.messageType
            2       , x1.requestId
            3       , x2.*
            4  from tmp_xml t
            5     , xmltable(
            6        '/request'
            7        passing t.object_value
            8        columns
            9          messageType  varchar2(10) path 'identification/messageType'
           10        , requestId    varchar2(10) path 'identification/requestId'
           11        , product_list xmltype      path 'product'
           12       ) x1
           13     , xmltable(
           14        '/product'
           15        passing x1.product_list
           16        columns
           17          productName  varchar2(10) path 'productName'
           18        , brandName    varchar2(10) path 'brandName'
           19       ) (+) x2
           20  ;
          ----------- ---------- ----------- ----------
          INVENTORY   29         N95         
          INVENTORY   29         ZAN         REL
          INVENTORY   29         ZAN         RE
          INVENTORY   29         ZAN         REL