2 Replies Latest reply: Oct 15, 2012 8:33 PM by user10863631 RSS

    What is this type of searching called?  Need assistance please.

    user10863631
      version: 11.2.0.2
      sparc64

      I have a requirement to create a view from an xmltype column. One of the columns would be:
      /Invoice/ReferenceNumbers/ReferenceNumberType=PURCHASE_ORDER_NUMBER/ReferenceNumber
      ...and the respective fragment would look like:
       <ReferenceNumbers>
          <ReferenceNumber>
             <ReferenceNumber>?</ReferenceNumber>
             <Description>?</Description>
             <ReferenceNumberType>?</ReferenceNumberType>
           </ReferenceNumber>
       </ReferenceNumbers>
      Being that the node is generically named "ReferenceNumber," what is it called when I need to search the "ReferenceNumberType" by a particular string - in this case, the PURCHASE_ORDER_NUMBER?

      select m.* 
          from INVOICE i,
               xmlTable
               (
                  '$i/Invoice'
                  passing i.doc as "i"
                  columns 
                  INVOICE_NUMBER PATH '/Invoice/InvoiceHeader/invoiceNumber/text()',
                  PO_NUMBER path '/Invoice/ReferenceNumbers/ReferenceNumberType=PURCHASE_ORDER_NUMBER/ReferenceNumber/text()'
               ) m
      /         
      
      /* I know this is incorrect, but trying to detail what I'm getting after */
      Any help would be appreciated...I'll research how to do that in sql xquery, I just don't know what it's called.

      Thank you in advance.