Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using XML clob in loop

Andrew RMay 18 2010 — edited Jul 18 2011
Hi,

I need to extract data from a given piece of XML. If I pass the xml to the procedure as clob and query it in a loop, it returns no rows. However if I include the actuall xml in the loop query it returns the data I'm looking for. Why doesn't it return data when using the clob?

1. Example code below works with xml included in the loop query:
BEGIN
   FOR x IN (    SELECT x.*
                   FROM XMLTABLE (
                           xmlnamespaces (
                              'http://schemas.xmlsoap.org/soap/envelope' AS "x",
                              'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"),
                           '*'
                           PASSING xmltype (
                                      '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Body>
      <loadServiceListResponse soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <loadServiceListReturn href="#id0"/>
      </loadServiceListResponse>
      <multiRef id="id0" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns1:ExternalSystemOutput" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:ns1="java:ExternalSystemOutput.TestIPA.nhsia.nhs">
         <standardOutput href="#id1"/>
         <systemData soapenc:arrayType="ns2:ExternalSystemData[2]" xsi:type="soapenc:Array" xmlns:ns2="java:ExternalSystemData.TestIPA.nhsia.nhs">
            <systemData href="#id2"/>
            <systemData href="#id3"/>
         </systemData>
      </multiRef>
      <multiRef id="id3" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns3:ExternalSystemData" xmlns:ns3="java:ExternalSystemData.TestIPA.nhsia.nhs" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
         <description xsi:type="soapenc:string">Prescription</description>
         <url xsi:type="soapenc:string">http://192.168.21.131:8080/nhsia/TestIPA/validate.jsp?sdwlhqw@5)vhuylfh@Suhvfulswlrq33509</url>
      </multiRef>
      <multiRef id="id1" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns4:StandardOutput" xmlns:ns4="java:StandardOutput.TestIPA.nhsia.nhs" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
         <auditID xsi:type="soapenc:string"/>
         <statusID xsi:type="soapenc:string">0</statusID>
         <systemAvailability xsi:type="soapenc:string">Available</systemAvailability>
      </multiRef>
      <multiRef id="id2" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns5:ExternalSystemData" xmlns:ns5="java:ExternalSystemData.TestIPA.nhsia.nhs" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
         <description xsi:type="soapenc:string">Appointment</description>
         <url xsi:type="soapenc:string">http://192.168.21.131:8080/nhsia/TestIPA/validate.jsp?vgzoktzC8,ykx|oikCGvvuotzsktz61409</url>
      </multiRef>
   </soapenv:Body>
</soapenv:Envelope>').
                                   EXTRACT (
                                      '//multiRef',
                                      'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"')
                           COLUMNS description VARCHAR2 (30) PATH 'description',
                                   url VARCHAR2 (250) PATH 'url') x)
   LOOP
      DBMS_OUTPUT.PUT_LINE('Rec:  '||x.description||' '||x.url);
   END LOOP;
END;
/
Returns: 
Rec:   
Rec:  Prescription http://192.168.21.131:8080/nhsia/TestIPA/validate.jsp?sdwlhqw@5)vhuylfh@Suhvfulswlrq33509
Rec:   
Rec:  Appointment http://192.168.21.131:8080/nhsia/TestIPA/validate.jsp?vgzoktzC8,ykx|oikCGvvuotzsktz61409
2. If I assign the XML to CLOB variable and reference the clob variable in loop query it returns no data:
DECLARE
   resp   CLOB :=              
                   '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Body>
      <loadServiceListResponse soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <loadServiceListReturn href="#id0"/>
      </loadServiceListResponse>
      <multiRef id="id0" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns1:ExternalSystemOutput" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:ns1="java:ExternalSystemOutput.TestIPA.nhsia.nhs">
         <standardOutput href="#id1"/>
         <systemData soapenc:arrayType="ns2:ExternalSystemData[2]" xsi:type="soapenc:Array" xmlns:ns2="java:ExternalSystemData.TestIPA.nhsia.nhs">
            <systemData href="#id2"/>
            <systemData href="#id3"/>
         </systemData>
      </multiRef>
      <multiRef id="id3" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns3:ExternalSystemData" xmlns:ns3="java:ExternalSystemData.TestIPA.nhsia.nhs" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
         <description xsi:type="soapenc:string">Prescription</description>
         <url xsi:type="soapenc:string">http://192.168.21.131:8080/nhsia/TestIPA/validate.jsp?sdwlhqw@5)vhuylfh@Suhvfulswlrq33509</url>
      </multiRef>
      <multiRef id="id1" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns4:StandardOutput" xmlns:ns4="java:StandardOutput.TestIPA.nhsia.nhs" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
         <auditID xsi:type="soapenc:string"/>
         <statusID xsi:type="soapenc:string">0</statusID>
         <systemAvailability xsi:type="soapenc:string">Available</systemAvailability>
      </multiRef>
      <multiRef id="id2" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns5:ExternalSystemData" xmlns:ns5="java:ExternalSystemData.TestIPA.nhsia.nhs" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
         <description xsi:type="soapenc:string">Appointment</description>
         <url xsi:type="soapenc:string">http://192.168.21.131:8080/nhsia/TestIPA/validate.jsp?vgzoktzC8,ykx|oikCGvvuotzsktz61409</url>
      </multiRef>
   </soapenv:Body>
</soapenv:Envelope>';
BEGIN
   FOR x IN (    SELECT x.*
                   FROM XMLTABLE (
                           xmlnamespaces (
                              'http://schemas.xmlsoap.org/soap/envelope' AS "x",
                              'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"),
                           '*'
                           PASSING xmltype (resp).
                                   EXTRACT (
                                      '//multiRef',
                                      'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"')
                           COLUMNS description VARCHAR2 (30) PATH 'description',
                                   url VARCHAR2 (250) PATH 'url') x)
   LOOP
      DBMS_OUTPUT.PUT_LINE('Rec:  '||x.description||' '||x.url);
   END LOOP;
END;
/
I would appreciate any help this one.

Cheers,

Andy.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 15 2011
Added on May 18 2010
#pl-sql-xml-programming, #xml
13 comments
3,986 views