Forum Stats

  • 3,734,283 Users
  • 2,246,938 Discussions
  • 7,857,218 Comments

Discussions

how get value from xml response ?

user7340885
user7340885 Member Posts: 12 Red Ribbon
edited Jan 27, 2021 12:26PM in Forms

hi every one

i am using oracle webservice to call oracle reports from procedure

it work fine and this is response xml docment i want get the job id from this

<?xml version="1.0" encoding="WINDOWS-1256"?>

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

 <SOAP-ENV:Body>

    <ns1:runJobResponse xmlns:ns1="urn:oracle-reports-rwclient-RWWebService" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">

   <return xsi:type="xsd:string">&lt;?xml version = &apos;1.0&apos; encoding = &apos;ISO-8859-1&apos; standalone = &apos;yes&apos;?&gt;

&lt;serverQueues&gt;

  &lt;job id=&quot;92&quot; queueType=&quot;current&quot;&gt;

   &lt;name&gt;D:\reports\bl_prt_inv.jsp&lt;/name&gt;

   &lt;type&gt;report&lt;/type&gt;

   &lt;status code=&quot;1&quot;&gt;انتظار في الطابور&lt;/status&gt;

   &lt;owner&gt;RWUser&lt;/owner&gt;

   &lt;server&gt;reportserv1&lt;/server&gt;

   &lt;destination&gt;

     &lt;desType&gt;file&lt;/desType&gt;

     &lt;desFormat&gt;pdf&lt;/desFormat&gt;

   &lt;/destination&gt;

   &lt;timingInfo&gt;

     &lt;queued&gt;26/01/2021 07:34:28 م&lt;/queued&gt;

     &lt;started&gt;26/01/2021 07:34:28 م&lt;/started&gt;

     &lt;finished&gt;26/01/2021 07:34:28 م&lt;/finished&gt;

   &lt;/timingInfo&gt;

  &lt;/job&gt;

&lt;/serverQueues&gt;

</return>

  </ns1:runJobResponse>

 </SOAP-ENV:Body>

</SOAP-ENV:Envelope>



i am tri to use this

select id from testxm, 

xmltable( '/serverQueues/job' passing testxm.xmfile

  columns id varchar2(100) path '@id') xml_tbl ; 

but it not work

any idea

regards ............

Best Answer

  • cormaco
    cormaco Member Posts: 1,559 Bronze Crown
    Accepted Answer

    This is how to get the value of id using SQL/XML functions:

    select 
        xmlcast(xmlquery('/serverQueues/job/@id' passing xmltype(retval) returning content) as varchar2(100)) as id
    from testxm, 
    xmltable(
        xmlnamespaces(
            'http://schemas.xmlsoap.org/soap/envelope/' as "SOAP-ENV",
            'urn:oracle-reports-rwclient-RWWebService'  as "ns1"
        ),
        '/SOAP-ENV:Envelope/SOAP-ENV:Body/ns1:runJobResponse' 
        passing testxm.xmfile
        columns
            retval clob path 'return/text()'
    )
    
    ID        
    ----------
    92
    
    
    
    user7340885

Answers

  • cormaco
    cormaco Member Posts: 1,559 Bronze Crown
    edited Jan 27, 2021 12:14PM

    Please post your example file as text not as a screenshot.

    user7340885
  • Michael Ferrante-Oracle
    Michael Ferrante-Oracle Senior Principal Product Manager USMember Posts: 6,644 Employee

    First, this is not the best place to be posting "how do I get a value from xml" questions. This is a "Forms" forum. That said, for the moment, I'm interested.

    Let's start by getting some basic information which you should always provide when first posting:

    .1. Exactly which version of Oracle Reports are you using?

    .2. Exactly which Oracle Database version are you using?

    .3. Please clarify what you are doing. You said, "...using oracle web service to call oracle reports...". Are you sure this is what you meant to say? Or are you actually calling a web service from reports (the reverse of what you stated)?

    If you are calling the web service from Reports, it might be easiest to parse the XML using Java. Create the needed parsing code in Java then use the Java Imported to wrap that Java code in plsql. You then can call that Java directly from plsql in your report.

  • cormaco
    cormaco Member Posts: 1,559 Bronze Crown
    Accepted Answer

    This is how to get the value of id using SQL/XML functions:

    select 
        xmlcast(xmlquery('/serverQueues/job/@id' passing xmltype(retval) returning content) as varchar2(100)) as id
    from testxm, 
    xmltable(
        xmlnamespaces(
            'http://schemas.xmlsoap.org/soap/envelope/' as "SOAP-ENV",
            'urn:oracle-reports-rwclient-RWWebService'  as "ns1"
        ),
        '/SOAP-ENV:Envelope/SOAP-ENV:Body/ns1:runJobResponse' 
        passing testxm.xmfile
        columns
            retval clob path 'return/text()'
    )
    
    ID        
    ----------
    92
    
    
    
    user7340885
  • user7340885
    user7340885 Member Posts: 12 Red Ribbon

    thanks a lot its work ...............

Sign In or Register to comment.