This discussion is archived
5 Replies Latest reply: Aug 28, 2012 4:19 AM by odie_63 RSS

Xpath functions in oracle

860250 Newbie
Currently Being Moderated
Hi,

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,
                    'Distributed',
                    cast(ServiceCalls.query('fn:local-name(.)') as varchar(100)),
                    1,
                    WIP.ServiceCalls.value('(TotalTime)[1]','int'),
                    WIP.ServiceCalls.value('(Status)[1]','varchar(100)'),
                    WIP.ServiceCalls.value('(Primary_deferred)[1]','varchar(20)'),
                    WIP.ServiceCalls.value('(FromCache)[1]','bit'),
                    @DateTimeStamp
          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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    We would need an exact database version to be able to give you some good advice...
  • 2. Re: Xpath functions in oracle
    odie_63 Guru
    Currently Being Moderated
    ... 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  ;
     
    NODENAME              TOTALTIME STATUS          PRIM_DEFER      FROM_CACHE START_TIME
    -------------------- ---------- --------------- --------------- ---------- ----------
    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
  • 3. Re: Xpath functions in oracle
    860250 Newbie
    Currently Being Moderated
    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(
    '//WIP/gatewayServiceCalls/mqdrop/Response/'
    passing v_wip
    COLUMNS
    nodename VARCHAR2(4000) path 'fn:local-name(.)'
    , respon VARCHAR2(4000) path 'Response'
    ) x;
  • 4. Re: Xpath functions in oracle
    860250 Newbie
    Currently Being Moderated
    Can any one help me on this
  • 5. Re: Xpath functions in oracle
    odie_63 Guru
    Currently Being Moderated
    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(
           '//WIP/gatewayServiceCalls/mqdrop'
           passing v_wip
           columns
             response   clob  path 'Response'
         ) x
    ;
    or, if you're expecting a single value then preferably :
    SELECT XMLCast(
             XMLQuery('//WIP/gatewayServiceCalls/mqdrop/Response' 
               passing v_wip
               returning content
             )
             as clob
           )
    FROM dual;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points