5 Replies Latest reply on Aug 28, 2012 11:19 AM by odie_63

    Xpath functions in oracle


      Is there any function like fn:local-name(.) in oracle XPATH functions. If yes help me wit ha simple example.

      I am new to Oracle & migrating procedure from SQL server to oracle where procedure taken XML script as input.

      I need to convert below SQL server select statement to Oracle.

      SELECT     converseUID,
                          cast(ServiceCalls.query('fn:local-name(.)') as varchar(100)),
                FROM     @WIP.nodes('//WIP/gatewayServiceCalls/*') as WIP(ServiceCalls)
                WHERE     WIP.ServiceCalls.value('(startTime)[1]','int') <> 0

      Thanks in advance.
        • 1. Re: Xpath functions in oracle
          Marco Gralike
          We would need an exact database version to be able to give you some good advice...
          • 2. Re: Xpath functions in oracle
            ... continued from SQL Forum : {thread:id=2431325}

            This should do it :
            SQL> SELECT x.*
              2  FROM XMLTable(
              3         '/gatewayServiceCalls/*'
              4         passing xmltype(bfilename('TEST_DIR','wip.xml'), nls_charset_id('AL32UTF8'))
              5         columns
              6           nodename   varchar2(20)  path 'fn:local-name(.)'
              7         , totaltime  number        path 'TotalTime'
              8         , status     varchar2(15)  path 'Status'
              9         , prim_defer varchar2(15)  path 'Primary_deferred'
             10         , from_cache varchar2(1)   path 'FromCache'
             11         , start_time number        path 'startTime'
             12       ) x
             13  WHERE start_time != 0
             14  ;
            -------------------- ---------- --------------- --------------- ---------- ----------
            mqdrop                     5466 SUCCESS         Primary                     640743750
            tranManLogRequest             0 SUCCESS         Deferred                    640743721
            tranManIsEnabled              0 SUCCESS         Primary         1           640743723
            XMLValidation                 6 SUCCESS         Primary                     640743743
            sqiwcall                      4 SUCCESS         Deferred                    640752535
            garageTransform               6 SUCCESS         Primary                     640743737
            6 rows selected
            NB : for my own convenience, I used the following to access the XML from my filesystem :
            xmltype(bfilename('TEST_DIR','wip.xml'), nls_charset_id('AL32UTF8'))
            In your case, use the column/variable holding the XML instead. Based on your other thread, I guess it's "v_wip"?

            Edited by: odie_63 on 24 août 2012 16:41
            1 person found this helpful
            • 3. Re: Xpath functions in oracle
              Thanks a lot for your help.

              I am trying to convert the below from sql server to oracle for the same v_wip input & in oracle its not working.

              @MQResponse1 = @WIP.value('(//WIP/gatewayServiceCalls/mqdrop/Response)[1]','varchar(max)')

              Below is the logic which I have used for oracle conversion.

              SELECT x.nodename, x.respon
              -- INTO v_servicename, v_MQResponse1
              FROM XMLTable(
              passing v_wip
              nodename VARCHAR2(4000) path 'fn:local-name(.)'
              , respon VARCHAR2(4000) path 'Response'
              ) x;
              • 4. Re: Xpath functions in oracle
                Can any one help me on this
                • 5. Re: Xpath functions in oracle
                  The PATH expression is wrong and, based on the sample XML you've posted earlier, VARCHAR2(4000) is not large enough to hold the extracted value.

                  You'll have to use CLOB datatype :
                  SELECT x.response
                  FROM XMLTable(
                         passing v_wip
                           response   clob  path 'Response'
                       ) x
                  or, if you're expecting a single value then preferably :
                  SELECT XMLCast(
                             passing v_wip
                             returning content
                           as clob
                  FROM dual;