2 Replies Latest reply: Aug 19, 2013 5:04 PM by user6836822 RSS

    Term comparison for InPath

    user6836822

      I have a clob column with XML data.

       

      <attrs><attr name="ESB_Availability_Status"><string>D</string></attr><attr name="ESB_Available_Stock"><int>0</int></attr><attr name="ESB_IsTaxable"><boolean>true</boolean></attr><attr name="ESB_isLeaseAvailable"><boolean>true</boolean></attr></attrs>

       

      When I use the following query it does not match and find any rows.

       

      SELECT

        extractValue(

          XmlType(attributes),

          '/attrs/attr[@name="ESB_Availability_Status"]/string'

        ) AS ESB_Availability_Status

      FROM

        MyTable

        WHERE

        CONTAINS(

          attributes,

          '{D} INPATH (/attrs/attr[@name="ESB_Availability_Status"]/string)'

        ) > 0

       

      But when I update the column with data like this with value P (or for that matter any other charcter N,DQ etc.). It retrieves data.

       

      <attrs><attr name="ESB_Availability_Status"><string>P</string></attr><attr name="ESB_Available_Stock"><int>0</int></attr><attr name="ESB_IsTaxable"><boolean>true</boolean></attr><attr name="ESB_isLeaseAvailable"><boolean>true</boolean></attr></attrs>

       

      SELECT

        extractValue(

          XmlType(attributes),

          '/attrs/attr[@name="ESB_Availability_Status"]/string'

        ) AS ESB_Availability_Status

      FROM

        MyTable

        WHERE

        CONTAINS(

          attributes,

          '{P} INPATH (/attrs/attr[@name="ESB_Availability_Status"]/string)'

        ) > 0

       

      What is happening with the comparison term?

        • 1. Re: Term comparison for InPath
          Jason_(A_Non)

          As this question has nothing to do with the XML DB, you have lowered your chance of getting the answer you seek.

           

          I think you might be looking for

          https://forums.oracle.com/community/developer/english/oracle_database/text

           

          Without knowing your version, or apparently having an index setup like you do what about something like

          SELECT 
            extractValue(
              XmlType(attributes),
              '/attrs/attr[@name="ESB_Availability_Status"]/string[text()="D"]'
            ) AS ESB_Availability_Status
          FROM
            MyTable
          

           

          which does return empty rows if the condition is not meet or

          SELECT *
            FROM (SELECT 
                  extractValue(
                    XmlType(attributes),
                    '/attrs/attr[@name="ESB_Availability_Status"]/string'
                  ) AS ESB_Availability_Status
                FROM
                  MyTable)
            WHERE ESB_Availability_Status = 'D';
          

           

          Of course there are also XMLTable/XQuery based approaches as well if you so desire.

          • 2. Re: Term comparison for InPath
            user6836822

            Thank Jason.

             

            1) I shall post in the suggested forum.

            2) I tried the variations suggested and they work as you mention and they return the D columns. My problem is that a java library is already coded to something like the original SQL I gave. The primary question is that how and why the query can find one data and not the other. If it can find P it can surely find D in the text node of the <string>.

             

            For other readers the select * approach with an inner query work, but in my case with large amounts of data, there is an immediate performance hit and the result slows down by 3 times.

             

            Thanks again for responding.