1 Reply Latest reply: Jul 10, 2012 9:22 AM by odie_63 RSS

    XML document parsing-- returns no data

    user12236189
      Database 10g R2
      <?xml version="1.0" encoding="UTF-8" ?>
      <request>
        <identification>
          <messageType>INVENTORY</messageType>
          <requestId>29</requestId>
        </identification>
        <product>
          <productName>N95</productName>
        </product>
        <product>
          <productName>ZAN</productName>
          <brandName>REL</brandName>
          <ndc>54-*0</ndc>
        </product>
        <product>
          <productName>ZAN</productName>
          <brandName>RE</brandName>
          <ndc>01-00</ndc>
        </product>
        <product>
          <productName>ZAN</productName>
          <brandName>REL</brandName>
          <ndc>01-01</ndc>
        </product>
      </request>
      select x1.messageType,x1.requestId
               , x2.productName,x1.produc
          from zz t
             , xmltable(
                 '/request'
                 passing t.zz
                 columns
                  messageType varchar2(50)         path 'identification/messageType'
                 ,requestId varchar2(30)         path 'identification/requestId'
                 ,produc xmltype   path 'product'
              )  x1
            , xmltable(
                'product'
                passing x1.produc
                columns
                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.

      Thanks,
        • 1. Re: XML document parsing-- returns no data
          odie_63
          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  ;
           
          MESSAGETYPE REQUESTID  PRODUCTNAME BRANDNAME
          ----------- ---------- ----------- ----------
          INVENTORY   29         N95         
          INVENTORY   29         ZAN         REL
          INVENTORY   29         ZAN         RE
          INVENTORY   29         ZAN         REL