8 Replies Latest reply: Jul 27, 2012 7:49 PM by MuhabbatAli RSS

    SOAP XML tag Query in SQL

    MuhabbatAli
      Hi , I have requirement to read XML data stored in a table and display it in Relational Format (Column).

      My XML file stored in table is;
      ===========================================
      <?xml version="1.0" encoding="UTF-8" ?>
      - <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:getGisWeatherResponse xmlns:ns1="http://oracle.j2ee.myservice/MyWebService.wsdl" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
      <return xsi:type="xsd:string"><?xml version = '1.0' encoding = 'UTF-8'?> <VW_GIS_WEATHER><Row><WEATHER_REPORT_DTS>12/15/2011 11:57:0</WEATHER_REPORT_DTS><WEATHER_TEMP_MAX>32</WEATHER_TEMP_MAX><WEATHER_TEMP_MIN>20</WEATHER_TEMP_MIN><WEATHER_BAROMETRIC_PRESS>null</WEATHER_BAROMETRIC_PRESS><WEATHER_WIND>null</WEATHER_WIND><WEATHER_VISIBILITY>null</WEATHER_VISIBILITY><WEATHER_REL_HUMIDITY>null</WEATHER_REL_HUMIDITY><WEATHER_SUNRISE>0709</WEATHER_SUNRISE><WEATHER_SUNSET>1746</WEATHER_SUNSET><WEATHER_MOONRISE>2219</WEATHER_MOONRISE><WEATHER_MOONSET>1032</WEATHER_MOONSET><WEATHER_TIDE_HIGH_WATER>null</WEATHER_TIDE_HIGH_WATER><WEATHER_TIDE_LOW_WATER>null</WEATHER_TIDE_LOW_WATER><WEATHER_HIGH_WATER>null</WEATHER_HIGH_WATER><WEATHER_LOW_WATER>null</WEATHER_LOW_WATER><WEATHER_STA_NAME>KARACHI</WEATHER_STA_NAME><WEATHER_STA_LAT>24.8</WEATHER_STA_LAT><WEATHER_STA_LONG>66.98</WEATHER_STA_LONG><CIVIL_TWILIGHT_FROM>0643</CIVIL_TWILIGHT_FROM><CIVIL_TWILIGHT_TO>1810</CIVIL_TWILIGHT_TO><NAUTICAL_TWILIGHT_FROM>0615</NAUTICAL_TWILIGHT_FROM><NAUTICAL_TWILIGHT_TO>1839</NAUTICAL_TWILIGHT_TO><ASTRO_TWILIGHT_FROM>0547</ASTRO_TWILIGHT_FROM><ASTRO_TWILIGHT_TO>1907</ASTRO_TWILIGHT_TO><WEATHER_STATE>SUNNY</WEATHER_STATE><SEA_STATE>CALM (GLASSY)</SEA_STATE><WIND_TYPE>GEOSTROPHIC</WIND_TYPE><CLOUD_TYPE>CUMULUS</CLOUD_TYPE></Row> <Row><WEATHER_REPORT_DTS>2/24/2011 11:8:0</WEATHER_REPORT_DTS><WEATHER_TEMP_MAX>null</WEATHER_TEMP_MAX><WEATHER_TEMP_MIN>null</WEATHER_TEMP_MIN><WEATHER_BAROMETRIC_PRESS>null</WEATHER_BAROMETRIC_PRESS><WEATHER_WIND>null</WEATHER_WIND><WEATHER_VISIBILITY>null</WEATHER_VISIBILITY><WEATHER_REL_HUMIDITY>null</WEATHER_REL_HUMIDITY><WEATHER_SUNRISE>0710</WEATHER_SUNRISE><WEATHER_SUNSET>1840</WEATHER_SUNSET><WEATHER_MOONRISE>0016</WEATHER_MOONRISE><WEATHER_MOONSET>1115</WEATHER_MOONSET><WEATHER_TIDE_HIGH_WATER>null</WEATHER_TIDE_HIGH_WATER><WEATHER_TIDE_LOW_WATER>null</WEATHER_TIDE_LOW_WATER><WEATHER_HIGH_WATER>null</WEATHER_HIGH_WATER><WEATHER_LOW_WATER>null</WEATHER_LOW_WATER><WEATHER_STA_NAME>JNB (ORMARA)</WEATHER_STA_NAME><WEATHER_STA_LAT>25.2</WEATHER_STA_LAT><WEATHER_STA_LONG>64.63</WEATHER_STA_LONG><CIVIL_TWILIGHT_FROM>0646</CIVIL_TWILIGHT_FROM><CIVIL_TWILIGHT_TO>1904</CIVIL_TWILIGHT_TO><NAUTICAL_TWILIGHT_FROM>0619</NAUTICAL_TWILIGHT_FROM><NAUTICAL_TWILIGHT_TO>1931</NAUTICAL_TWILIGHT_TO><ASTRO_TWILIGHT_FROM>0553</ASTRO_TWILIGHT_FROM><ASTRO_TWILIGHT_TO>1957</ASTRO_TWILIGHT_TO><WEATHER_STATE>SUNNY</WEATHER_STATE><SEA_STATE>SLIGHT</SEA_STATE><WIND_TYPE>GRADIENT</WIND_TYPE><CLOUD_TYPE>LOW CLOUDS</CLOUD_TYPE></Row> <Row><WEATHER_REPORT_DTS>10/4/2011 14:34:0</WEATHER_REPORT_DTS><WEATHER_TEMP_MAX>null</WEATHER_TEMP_MAX><WEATHER_TEMP_MIN>null</WEATHER_TEMP_MIN><WEATHER_BAROMETRIC_PRESS>null</WEATHER_BAROMETRIC_PRESS><WEATHER_WIND>null</WEATHER_WIND><WEATHER_VISIBILITY>null</WEATHER_VISIBILITY><WEATHER_REL_HUMIDITY>null</WEATHER_REL_HUMIDITY><WEATHER_SUNRISE>0625</WEATHER_SUNRISE><WEATHER_SUNSET>1816</WEATHER_SUNSET><WEATHER_MOONRISE>1319</WEATHER_MOONRISE><WEATHER_MOONSET>null</WEATHER_MOONSET><WEATHER_TIDE_HIGH_WATER>null</WEATHER_TIDE_HIGH_WATER><WEATHER_TIDE_LOW_WATER>null</WEATHER_TIDE_LOW_WATER><WEATHER_HIGH_WATER>null</WEATHER_HIGH_WATER><WEATHER_LOW_WATER>null</WEATHER_LOW_WATER><WEATHER_STA_NAME>KARACHI</WEATHER_STA_NAME><WEATHER_STA_LAT>24.8</WEATHER_STA_LAT><WEATHER_STA_LONG>66.98</WEATHER_STA_LONG><CIVIL_TWILIGHT_FROM>0602</CIVIL_TWILIGHT_FROM><CIVIL_TWILIGHT_TO>1839</CIVIL_TWILIGHT_TO><NAUTICAL_TWILIGHT_FROM>0536</NAUTICAL_TWILIGHT_FROM><NAUTICAL_TWILIGHT_TO>1906</NAUTICAL_TWILIGHT_TO><ASTRO_TWILIGHT_FROM>0509</ASTRO_TWILIGHT_FROM><ASTRO_TWILIGHT_TO>1932</ASTRO_TWILIGHT_TO><WEATHER_STATE>CLOUDY</WEATHER_STATE><SEA_STATE>MODERATE</SEA_STATE><WIND_TYPE>GRADIENT</WIND_TYPE><CLOUD_TYPE>LOW CLOUDS</CLOUD_TYPE></Row> <Row><WEATHER_REPORT_DTS>12/14/2011 0:0:0</WEATHER_REPORT_DTS><WEATHER_TEMP_MAX>25</WEATHER_TEMP_MAX><WEATHER_TEMP_MIN>12</WEATHER_TEMP_MIN><WEATHER_BAROMETRIC_PRESS>100</WEATHER_BAROMETRIC_PRESS><WEATHER_WIND>null</WEATHER_WIND><WEATHER_VISIBILITY>null</WEATHER_VISIBILITY><WEATHER_REL_HUMIDITY>null</WEATHER_REL_HUMIDITY><WEATHER_SUNRISE>0718</WEATHER_SUNRISE><WEATHER_SUNSET>1754</WEATHER_SUNSET><WEATHER_MOONRISE>2130</WEATHER_MOONRISE><WEATHER_MOONSET>1002</WEATHER_MOONSET><WEATHER_TIDE_HIGH_WATER>null</WEATHER_TIDE_HIGH_WATER><WEATHER_TIDE_LOW_WATER>null</WEATHER_TIDE_LOW_WATER><WEATHER_HIGH_WATER>null</WEATHER_HIGH_WATER><WEATHER_LOW_WATER>null</WEATHER_LOW_WATER><WEATHER_STA_NAME>JNB (ORMARA)</WEATHER_STA_NAME><WEATHER_STA_LAT>25.2</WEATHER_STA_LAT><WEATHER_STA_LONG>64.63</WEATHER_STA_LONG><CIVIL_TWILIGHT_FROM>0653</CIVIL_TWILIGHT_FROM><CIVIL_TWILIGHT_TO>1818</CIVIL_TWILIGHT_TO><NAUTICAL_TWILIGHT_FROM>0624</NAUTICAL_TWILIGHT_FROM><NAUTICAL_TWILIGHT_TO>1847</NAUTICAL_TWILIGHT_TO><ASTRO_TWILIGHT_FROM>0557</ASTRO_TWILIGHT_FROM><ASTRO_TWILIGHT_TO>1915</ASTRO_TWILIGHT_TO><WEATHER_STATE>CLOUDY</WEATHER_STATE><SEA_STATE>MODERATE</SEA_STATE><WIND_TYPE>GEOSTROPHIC</WIND_TYPE><CLOUD_TYPE>CUMULUS</CLOUD_TYPE></Row> <Row><WEATHER_REPORT_DTS>12/12/2011 0:0:0</WEATHER_REPORT_DTS><WEATHER_TEMP_MAX>30</WEATHER_TEMP_MAX><WEATHER_TEMP_MIN>12</WEATHER_TEMP_MIN><WEATHER_BAROMETRIC_PRESS>null</WEATHER_BAROMETRIC_PRESS><WEATHER_WIND>null</WEATHER_WIND><WEATHER_VISIBILITY>null</WEATHER_VISIBILITY><WEATHER_REL_HUMIDITY>null</WEATHER_REL_HUMIDITY><WEATHER_SUNRISE>0727</WEATHER_SUNRISE><WEATHER_SUNSET>1802</WEATHER_SUNSET><WEATHER_MOONRISE>1944</WEATHER_MOONRISE><WEATHER_MOONSET>0842</WEATHER_MOONSET><WEATHER_TIDE_HIGH_WATER>null</WEATHER_TIDE_HIGH_WATER><WEATHER_TIDE_LOW_WATER>null</WEATHER_TIDE_LOW_WATER><WEATHER_HIGH_WATER>null</WEATHER_HIGH_WATER><WEATHER_LOW_WATER>null</WEATHER_LOW_WATER><WEATHER_STA_NAME>AKRAM (GAWADAR)</WEATHER_STA_NAME><WEATHER_STA_LAT>25.2333</WEATHER_STA_LAT><WEATHER_STA_LONG>62.3295</WEATHER_STA_LONG><CIVIL_TWILIGHT_FROM>0701</CIVIL_TWILIGHT_FROM><CIVIL_TWILIGHT_TO>1827</CIVIL_TWILIGHT_TO><NAUTICAL_TWILIGHT_FROM>0633</NAUTICAL_TWILIGHT_FROM><NAUTICAL_TWILIGHT_TO>1856</NAUTICAL_TWILIGHT_TO><ASTRO_TWILIGHT_FROM>0605</ASTRO_TWILIGHT_FROM><ASTRO_TWILIGHT_TO>1924</ASTRO_TWILIGHT_TO><WEATHER_STATE>SUNNY</WEATHER_STATE><SEA_STATE>CALM (GLASSY)</SEA_STATE><WIND_TYPE>GRADIENT</WIND_TYPE><CLOUD_TYPE>CUMULUS</CLOUD_TYPE></Row> <Row><WEATHER_REPORT_DTS>12/13/2011 0:0:0</WEATHER_REPORT_DTS><WEATHER_TEMP_MAX>35</WEATHER_TEMP_MAX><WEATHER_TEMP_MIN>15</WEATHER_TEMP_MIN><WEATHER_BAROMETRIC_PRESS>null</WEATHER_BAROMETRIC_PRESS><WEATHER_WIND>null</WEATHER_WIND><WEATHER_VISIBILITY>null</WEATHER_VISIBILITY><WEATHER_REL_HUMIDITY>null</WEATHER_REL_HUMIDITY><WEATHER_SUNRISE>0729</WEATHER_SUNRISE><WEATHER_SUNSET>1805</WEATHER_SUNSET><WEATHER_MOONRISE>2044</WEATHER_MOONRISE><WEATHER_MOONSET>0930</WEATHER_MOONSET><WEATHER_TIDE_HIGH_WATER>null</WEATHER_TIDE_HIGH_WATER><WEATHER_TIDE_LOW_WATER>null</WEATHER_TIDE_LOW_WATER><WEATHER_HIGH_WATER>null</WEATHER_HIGH_WATER><WEATHER_LOW_WATER>null</WEATHER_LOW_WATER><WEATHER_STA_NAME>JIWANI</WEATHER_STA_NAME><WEATHER_STA_LAT>25.007</WEATHER_STA_LAT><WEATHER_STA_LONG>61.8</WEATHER_STA_LONG><CIVIL_TWILIGHT_FROM>0704</CIVIL_TWILIGHT_FROM><CIVIL_TWILIGHT_TO>1830</CIVIL_TWILIGHT_TO><NAUTICAL_TWILIGHT_FROM>0635</NAUTICAL_TWILIGHT_FROM><NAUTICAL_TWILIGHT_TO>1858</NAUTICAL_TWILIGHT_TO><ASTRO_TWILIGHT_FROM>0607</ASTRO_TWILIGHT_FROM><ASTRO_TWILIGHT_TO>1927</ASTRO_TWILIGHT_TO><WEATHER_STATE>SUNNY</WEATHER_STATE><SEA_STATE>CALM (GLASSY)</SEA_STATE><WIND_TYPE>null</WIND_TYPE><CLOUD_TYPE>null</CLOUD_TYPE></Row> <Row><WEATHER_REPORT_DTS>12/13/2011 0:0:0</WEATHER_REPORT_DTS><WEATHER_TEMP_MAX>24</WEATHER_TEMP_MAX><WEATHER_TEMP_MIN>10</WEATHER_TEMP_MIN><WEATHER_BAROMETRIC_PRESS>null</WEATHER_BAROMETRIC_PRESS><WEATHER_WIND>null</WEATHER_WIND><WEATHER_VISIBILITY>null</WEATHER_VISIBILITY><WEATHER_REL_HUMIDITY>null</WEATHER_REL_HUMIDITY><WEATHER_SUNRISE>0723</WEATHER_SUNRISE><WEATHER_SUNSET>1758</WEATHER_SUNSET><WEATHER_MOONRISE>2037</WEATHER_MOONRISE><WEATHER_MOONSET>0924</WEATHER_MOONSET><WEATHER_TIDE_HIGH_WATER>null</WEATHER_TIDE_HIGH_WATER><WEATHER_TIDE_LOW_WATER>null</WEATHER_TIDE_LOW_WATER><WEATHER_HIGH_WATER>null</WEATHER_HIGH_WATER><WEATHER_LOW_WATER>null</WEATHER_LOW_WATER><WEATHER_STA_NAME>MAKRAN</WEATHER_STA_NAME><WEATHER_STA_LAT>25.15</WEATHER_STA_LAT><WEATHER_STA_LONG>63.38333333333333333333333333333333333</WEATHER_STA_LONG><CIVIL_TWILIGHT_FROM>0657</CIVIL_TWILIGHT_FROM><CIVIL_TWILIGHT_TO>1823</CIVIL_TWILIGHT_TO><NAUTICAL_TWILIGHT_FROM>0629</NAUTICAL_TWILIGHT_FROM><NAUTICAL_TWILIGHT_TO>1852</NAUTICAL_TWILIGHT_TO><ASTRO_TWILIGHT_FROM>0601</ASTRO_TWILIGHT_FROM><ASTRO_TWILIGHT_TO>1920</ASTRO_TWILIGHT_TO><WEATHER_STATE>SUNNY</WEATHER_STATE><SEA_STATE>null</SEA_STATE><WIND_TYPE>null</WIND_TYPE><CLOUD_TYPE>null</CLOUD_TYPE></Row> <Row><WEATHER_REPORT_DTS>12/15/2011 0:0:0</WEATHER_REPORT_DTS><WEATHER_TEMP_MAX>19</WEATHER_TEMP_MAX><WEATHER_TEMP_MIN>12</WEATHER_TEMP_MIN><WEATHER_BAROMETRIC_PRESS>null</WEATHER_BAROMETRIC_PRESS><WEATHER_WIND>null</WEATHER_WIND><WEATHER_VISIBILITY>null</WEATHER_VISIBILITY><WEATHER_REL_HUMIDITY>null</WEATHER_REL_HUMIDITY><WEATHER_SUNRISE>0719</WEATHER_SUNRISE><WEATHER_SUNSET>1754</WEATHER_SUNSET><WEATHER_MOONRISE>2228</WEATHER_MOONRISE><WEATHER_MOONSET>1042</WEATHER_MOONSET><WEATHER_TIDE_HIGH_WATER>null</WEATHER_TIDE_HIGH_WATER><WEATHER_TIDE_LOW_WATER>null</WEATHER_TIDE_LOW_WATER><WEATHER_HIGH_WATER>null</WEATHER_HIGH_WATER><WEATHER_LOW_WATER>null</WEATHER_LOW_WATER><WEATHER_STA_NAME>JNB (ORMARA)</WEATHER_STA_NAME><WEATHER_STA_LAT>25.2</WEATHER_STA_LAT><WEATHER_STA_LONG>64.63</WEATHER_STA_LONG><CIVIL_TWILIGHT_FROM>0654</CIVIL_TWILIGHT_FROM><CIVIL_TWILIGHT_TO>1819</CIVIL_TWILIGHT_TO><NAUTICAL_TWILIGHT_FROM>0625</NAUTICAL_TWILIGHT_FROM><NAUTICAL_TWILIGHT_TO>1847</NAUTICAL_TWILIGHT_TO><ASTRO_TWILIGHT_FROM>0557</ASTRO_TWILIGHT_FROM><ASTRO_TWILIGHT_TO>1916</ASTRO_TWILIGHT_TO><WEATHER_STATE>SUNNY</WEATHER_STATE><SEA_STATE>CALM (GLASSY)</SEA_STATE><WIND_TYPE>null</WIND_TYPE><CLOUD_TYPE>HIGH CLOUDS</CLOUD_TYPE></Row> </VW_GIS_WEATHER></return>
      </ns1:getGisWeatherResponse>
      </SOAP-ENV:Body>
      </SOAP-ENV:Envelope>


      =================================================
      and my SQL query is given below;
      SELECT x.*
      FROM temp_xml
      , XMLTable(
      XMLNamespaces(
      'http://schemas.xmlsoap.org/soap/envelope/' as "SOAP-ENV"
      , 'http://oracle.j2ee.myservice/MyWebService.wsdl' AS "ns1"
      )
      ,
      '/SOAP-ENV:Envelope/SOAP-ENV:Body/ns1:getGisWeatherResponse/VW_GIS_WEATHER/Row'
      PASSING XMLTYPE(xml)
      COLUMNS WIND_TYPE VARCHAR2(100) PATH 'WIND_TYPE'
      , CLOUD_TYPE VARCHAR2(100) PATH 'CLOUD_TYPE'
      , WEATHER_STA_NAME VARCHAR2(150) PATH 'WEATHER_STA_NAME'
      ) x

      I want to get all the rows of "VW_GIS_WEATHER" view.

      Regards
        • 1. Re: SOAP XML tag Query in SQL
          odie_63
          Hi,

          1) Please give your database version, it's important (SELECT * FROM v$version).

          2) Do not copy/paste XML content from your browser. So please, post again the content of your file using a plain text editor, or use the "view source" feature of your browser.

          I suspect the SOAP envelope to embed the response XML payload in its escaped form (as a string, not XML).
          That means you first have to extract it from the text() node, unescape it, then feed it to XMLTable.

          Here's a similar situation from a recent thread : {thread:id=2390962}
          • 2. Re: SOAP XML tag Query in SQL
            MuhabbatAli
            Hi Thanks for the Response.

            My database version is 10.2.0.1.0

            You are right, My xml is given below from the IE (View source option);
            =============xml=================
            <?xml version='1.0' encoding='UTF-8'?>
            <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:getGisWeatherResponse xmlns:ns1="http://oracle.j2ee.myservice/MyWebService.wsdl" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
            <return xsi:type="xsd:string"> &lt;?xml version = &apos;1.0&apos; encoding = &apos;UTF-8&apos;?&gt;
            &lt;VW_GIS_WEATHER&gt;&lt;Row&gt;&lt;WEATHER_REPORT_DTS&gt;12/15/2011 11:57:0&lt;/WEATHER_REPORT_DTS&gt;&lt;WEATHER_TEMP_MAX&gt;32&lt;/WEATHER_TEMP_MAX&gt;&lt;WEATHER_TEMP_MIN&gt;20&lt;/WEATHER_TEMP_MIN&gt;&lt;WEATHER_BAROMETRIC_PRESS&gt;null&lt;/WEATHER_BAROMETRIC_PRESS&gt;&lt;WEATHER_WIND&gt;null&lt;/WEATHER_WIND&gt;&lt;WEATHER_VISIBILITY&gt;null&lt;/WEATHER_VISIBILITY&gt;&lt;WEATHER_REL_HUMIDITY&gt;null&lt;/WEATHER_REL_HUMIDITY&gt;&lt;WEATHER_SUNRISE&gt;0709&lt;/WEATHER_SUNRISE&gt;&lt;WEATHER_SUNSET&gt;1746&lt;/WEATHER_SUNSET&gt;&lt;WEATHER_MOONRISE&gt;2219&lt;/WEATHER_MOONRISE&gt;&lt;WEATHER_MOONSET&gt;1032&lt;/WEATHER_MOONSET&gt;&lt;WEATHER_TIDE_HIGH_WATER&gt;null&lt;/WEATHER_TIDE_HIGH_WATER&gt;&lt;WEATHER_TIDE_LOW_WATER&gt;null&lt;/WEATHER_TIDE_LOW_WATER&gt;&lt;WEATHER_HIGH_WATER&gt;null&lt;/WEATHER_HIGH_WATER&gt;&lt;WEATHER_LOW_WATER&gt;null&lt;/WEATHER_LOW_WATER&gt;&lt;WEATHER_STA_NAME&gt;KARACHI&lt;/WEATHER_STA_NAME&gt;&lt;WEATHER_STA_LAT&gt;24.8&lt;/WEATHER_STA_LAT&gt;&lt;WEATHER_STA_LONG&gt;66.98&lt;/WEATHER_STA_LONG&gt;&lt;CIVIL_TWILIGHT_FROM&gt;0643&lt;/CIVIL_TWILIGHT_FROM&gt;&lt;CIVIL_TWILIGHT_TO&gt;1810&lt;/CIVIL_TWILIGHT_TO&gt;&lt;NAUTICAL_TWILIGHT_FROM&gt;0615&lt;/NAUTICAL_TWILIGHT_FROM&gt;&lt;NAUTICAL_TWILIGHT_TO&gt;1839&lt;/NAUTICAL_TWILIGHT_TO&gt;&lt;ASTRO_TWILIGHT_FROM&gt;0547&lt;/ASTRO_TWILIGHT_FROM&gt;&lt;ASTRO_TWILIGHT_TO&gt;1907&lt;/ASTRO_TWILIGHT_TO&gt;&lt;WEATHER_STATE&gt;SUNNY&lt;/WEATHER_STATE&gt;&lt;SEA_STATE&gt;CALM (GLASSY)&lt;/SEA_STATE&gt;&lt;WIND_TYPE&gt;GEOSTROPHIC&lt;/WIND_TYPE&gt;&lt;CLOUD_TYPE&gt;CUMULUS&lt;/CLOUD_TYPE&gt;&lt;/Row&gt; &lt;Row&gt;&lt;WEATHER_REPORT_DTS&gt;2/24/2011 11:8:0&lt;/WEATHER_REPORT_DTS&gt;&lt;WEATHER_TEMP_MAX&gt;null&lt;/WEATHER_TEMP_MAX&gt;&lt;WEATHER_TEMP_MIN&gt;null&lt;/WEATHER_TEMP_MIN&gt;&lt;WEATHER_BAROMETRIC_PRESS&gt;null&lt;/WEATHER_BAROMETRIC_PRESS&gt;&lt;WEATHER_WIND&gt;null&lt;/WEATHER_WIND&gt;&lt;WEATHER_VISIBILITY&gt;null&lt;/WEATHER_VISIBILITY&gt;&lt;WEATHER_REL_HUMIDITY&gt;null&lt;/WEATHER_REL_HUMIDITY&gt;&lt;WEATHER_SUNRISE&gt;0710&lt;/WEATHER_SUNRISE&gt;&lt;WEATHER_SUNSET&gt;1840&lt;/WEATHER_SUNSET&gt;&lt;WEATHER_MOONRISE&gt;0016&lt;/WEATHER_MOONRISE&gt;&lt;WEATHER_MOONSET&gt;1115&lt;/WEATHER_MOONSET&gt;&lt;WEATHER_TIDE_HIGH_WATER&gt;null&lt;/WEATHER_TIDE_HIGH_WATER&gt;&lt;WEATHER_TIDE_LOW_WATER&gt;null&lt;/WEATHER_TIDE_LOW_WATER&gt;&lt;WEATHER_HIGH_WATER&gt;null&lt;/WEATHER_HIGH_WATER&gt;&lt;WEATHER_LOW_WATER&gt;null&lt;/WEATHER_LOW_WATER&gt;&lt;WEATHER_STA_NAME&gt;JNB (ORMARA)&lt;/WEATHER_STA_NAME&gt;&lt;WEATHER_STA_LAT&gt;25.2&lt;/WEATHER_STA_LAT&gt;&lt;WEATHER_STA_LONG&gt;64.63&lt;/WEATHER_STA_LONG&gt;&lt;CIVIL_TWILIGHT_FROM&gt;0646&lt;/CIVIL_TWILIGHT_FROM&gt;&lt;CIVIL_TWILIGHT_TO&gt;1904&lt;/CIVIL_TWILIGHT_TO&gt;&lt;NAUTICAL_TWILIGHT_FROM&gt;0619&lt;/NAUTICAL_TWILIGHT_FROM&gt;&lt;NAUTICAL_TWILIGHT_TO&gt;1931&lt;/NAUTICAL_TWILIGHT_TO&gt;&lt;ASTRO_TWILIGHT_FROM&gt;0553&lt;/ASTRO_TWILIGHT_FROM&gt;&lt;ASTRO_TWILIGHT_TO&gt;1957&lt;/ASTRO_TWILIGHT_TO&gt;&lt;WEATHER_STATE&gt;SUNNY&lt;/WEATHER_STATE&gt;&lt;SEA_STATE&gt;SLIGHT&lt;/SEA_STATE&gt;&lt;WIND_TYPE&gt;GRADIENT&lt;/WIND_TYPE&gt;&lt;CLOUD_TYPE&gt;LOW CLOUDS&lt;/CLOUD_TYPE&gt;&lt;/Row&gt; &lt;Row&gt;&lt;WEATHER_REPORT_DTS&gt;10/4/2011 14:34:0&lt;/WEATHER_REPORT_DTS&gt;&lt;WEATHER_TEMP_MAX&gt;null&lt;/WEATHER_TEMP_MAX&gt;&lt;WEATHER_TEMP_MIN&gt;null&lt;/WEATHER_TEMP_MIN&gt;&lt;WEATHER_BAROMETRIC_PRESS&gt;null&lt;/WEATHER_BAROMETRIC_PRESS&gt;&lt;WEATHER_WIND&gt;null&lt;/WEATHER_WIND&gt;&lt;WEATHER_VISIBILITY&gt;null&lt;/WEATHER_VISIBILITY&gt;&lt;WEATHER_REL_HUMIDITY&gt;null&lt;/WEATHER_REL_HUMIDITY&gt;&lt;WEATHER_SUNRISE&gt;0625&lt;/WEATHER_SUNRISE&gt;&lt;WEATHER_SUNSET&gt;1816&lt;/WEATHER_SUNSET&gt;&lt;WEATHER_MOONRISE&gt;1319&lt;/WEATHER_MOONRISE&gt;&lt;WEATHER_MOONSET&gt;null&lt;/WEATHER_MOONSET&gt;&lt;WEATHER_TIDE_HIGH_WATER&gt;null&lt;/WEATHER_TIDE_HIGH_WATER&gt;&lt;WEATHER_TIDE_LOW_WATER&gt;null&lt;/WEATHER_TIDE_LOW_WATER&gt;&lt;WEATHER_HIGH_WATER&gt;null&lt;/WEATHER_HIGH_WATER&gt;&lt;WEATHER_LOW_WATER&gt;null&lt;/WEATHER_LOW_WATER&gt;&lt;WEATHER_STA_NAME&gt;KARACHI&lt;/WEATHER_STA_NAME&gt;&lt;WEATHER_STA_LAT&gt;24.8&lt;/WEATHER_STA_LAT&gt;&lt;WEATHER_STA_LONG&gt;66.98&lt;/WEATHER_STA_LONG&gt;&lt;CIVIL_TWILIGHT_FROM&gt;0602&lt;/CIVIL_TWILIGHT_FROM&gt;&lt;CIVIL_TWILIGHT_TO&gt;1839&lt;/CIVIL_TWILIGHT_TO&gt;&lt;NAUTICAL_TWILIGHT_FROM&gt;0536&lt;/NAUTICAL_TWILIGHT_FROM&gt;&lt;NAUTICAL_TWILIGHT_TO&gt;1906&lt;/NAUTICAL_TWILIGHT_TO&gt;&lt;ASTRO_TWILIGHT_FROM&gt;0509&lt;/ASTRO_TWILIGHT_FROM&gt;&lt;ASTRO_TWILIGHT_TO&gt;1932&lt;/ASTRO_TWILIGHT_TO&gt;&lt;WEATHER_STATE&gt;CLOUDY&lt;/WEATHER_STATE&gt;&lt;SEA_STATE&gt;MODERATE&lt;/SEA_STATE&gt;&lt;WIND_TYPE&gt;GRADIENT&lt;/WIND_TYPE&gt;&lt;CLOUD_TYPE&gt;LOW CLOUDS&lt;/CLOUD_TYPE&gt;&lt;/Row&gt; &lt;Row&gt;&lt;WEATHER_REPORT_DTS&gt;12/14/2011 0:0:0&lt;/WEATHER_REPORT_DTS&gt;&lt;WEATHER_TEMP_MAX&gt;25&lt;/WEATHER_TEMP_MAX&gt;&lt;WEATHER_TEMP_MIN&gt;12&lt;/WEATHER_TEMP_MIN&gt;&lt;WEATHER_BAROMETRIC_PRESS&gt;100&lt;/WEATHER_BAROMETRIC_PRESS&gt;&lt;WEATHER_WIND&gt;null&lt;/WEATHER_WIND&gt;&lt;WEATHER_VISIBILITY&gt;null&lt;/WEATHER_VISIBILITY&gt;&lt;WEATHER_REL_HUMIDITY&gt;null&lt;/WEATHER_REL_HUMIDITY&gt;&lt;WEATHER_SUNRISE&gt;0718&lt;/WEATHER_SUNRISE&gt;&lt;WEATHER_SUNSET&gt;1754&lt;/WEATHER_SUNSET&gt;&lt;WEATHER_MOONRISE&gt;2130&lt;/WEATHER_MOONRISE&gt;&lt;WEATHER_MOONSET&gt;1002&lt;/WEATHER_MOONSET&gt;&lt;WEATHER_TIDE_HIGH_WATER&gt;null&lt;/WEATHER_TIDE_HIGH_WATER&gt;&lt;WEATHER_TIDE_LOW_WATER&gt;null&lt;/WEATHER_TIDE_LOW_WATER&gt;&lt;WEATHER_HIGH_WATER&gt;null&lt;/WEATHER_HIGH_WATER&gt;&lt;WEATHER_LOW_WATER&gt;null&lt;/WEATHER_LOW_WATER&gt;&lt;WEATHER_STA_NAME&gt;JNB (ORMARA)&lt;/WEATHER_STA_NAME&gt;&lt;WEATHER_STA_LAT&gt;25.2&lt;/WEATHER_STA_LAT&gt;&lt;WEATHER_STA_LONG&gt;64.63&lt;/WEATHER_STA_LONG&gt;&lt;CIVIL_TWILIGHT_FROM&gt;0653&lt;/CIVIL_TWILIGHT_FROM&gt;&lt;CIVIL_TWILIGHT_TO&gt;1818&lt;/CIVIL_TWILIGHT_TO&gt;&lt;NAUTICAL_TWILIGHT_FROM&gt;0624&lt;/NAUTICAL_TWILIGHT_FROM&gt;&lt;NAUTICAL_TWILIGHT_TO&gt;1847&lt;/NAUTICAL_TWILIGHT_TO&gt;&lt;ASTRO_TWILIGHT_FROM&gt;0557&lt;/ASTRO_TWILIGHT_FROM&gt;&lt;ASTRO_TWILIGHT_TO&gt;1915&lt;/ASTRO_TWILIGHT_TO&gt;&lt;WEATHER_STATE&gt;CLOUDY&lt;/WEATHER_STATE&gt;&lt;SEA_STATE&gt;MODERATE&lt;/SEA_STATE&gt;&lt;WIND_TYPE&gt;GEOSTROPHIC&lt;/WIND_TYPE&gt;&lt;CLOUD_TYPE&gt;CUMULUS&lt;/CLOUD_TYPE&gt;&lt;/Row&gt; &lt;Row&gt;&lt;WEATHER_REPORT_DTS&gt;12/12/2011 0:0:0&lt;/WEATHER_REPORT_DTS&gt;&lt;WEATHER_TEMP_MAX&gt;30&lt;/WEATHER_TEMP_MAX&gt;&lt;WEATHER_TEMP_MIN&gt;12&lt;/WEATHER_TEMP_MIN&gt;&lt;WEATHER_BAROMETRIC_PRESS&gt;null&lt;/WEATHER_BAROMETRIC_PRESS&gt;&lt;WEATHER_WIND&gt;null&lt;/WEATHER_WIND&gt;&lt;WEATHER_VISIBILITY&gt;null&lt;/WEATHER_VISIBILITY&gt;&lt;WEATHER_REL_HUMIDITY&gt;null&lt;/WEATHER_REL_HUMIDITY&gt;&lt;WEATHER_SUNRISE&gt;0727&lt;/WEATHER_SUNRISE&gt;&lt;WEATHER_SUNSET&gt;1802&lt;/WEATHER_SUNSET&gt;&lt;WEATHER_MOONRISE&gt;1944&lt;/WEATHER_MOONRISE&gt;&lt;WEATHER_MOONSET&gt;0842&lt;/WEATHER_MOONSET&gt;&lt;WEATHER_TIDE_HIGH_WATER&gt;null&lt;/WEATHER_TIDE_HIGH_WATER&gt;&lt;WEATHER_TIDE_LOW_WATER&gt;null&lt;/WEATHER_TIDE_LOW_WATER&gt;&lt;WEATHER_HIGH_WATER&gt;null&lt;/WEATHER_HIGH_WATER&gt;&lt;WEATHER_LOW_WATER&gt;null&lt;/WEATHER_LOW_WATER&gt;&lt;WEATHER_STA_NAME&gt;AKRAM (GAWADAR)&lt;/WEATHER_STA_NAME&gt;&lt;WEATHER_STA_LAT&gt;25.2333&lt;/WEATHER_STA_LAT&gt;&lt;WEATHER_STA_LONG&gt;62.3295&lt;/WEATHER_STA_LONG&gt;&lt;CIVIL_TWILIGHT_FROM&gt;0701&lt;/CIVIL_TWILIGHT_FROM&gt;&lt;CIVIL_TWILIGHT_TO&gt;1827&lt;/CIVIL_TWILIGHT_TO&gt;&lt;NAUTICAL_TWILIGHT_FROM&gt;0633&lt;/NAUTICAL_TWILIGHT_FROM&gt;&lt;NAUTICAL_TWILIGHT_TO&gt;1856&lt;/NAUTICAL_TWILIGHT_TO&gt;&lt;ASTRO_TWILIGHT_FROM&gt;0605&lt;/ASTRO_TWILIGHT_FROM&gt;&lt;ASTRO_TWILIGHT_TO&gt;1924&lt;/ASTRO_TWILIGHT_TO&gt;&lt;WEATHER_STATE&gt;SUNNY&lt;/WEATHER_STATE&gt;&lt;SEA_STATE&gt;CALM (GLASSY)&lt;/SEA_STATE&gt;&lt;WIND_TYPE&gt;GRADIENT&lt;/WIND_TYPE&gt;&lt;CLOUD_TYPE&gt;CUMULUS&lt;/CLOUD_TYPE&gt;&lt;/Row&gt; &lt;Row&gt;&lt;WEATHER_REPORT_DTS&gt;12/13/2011 0:0:0&lt;/WEATHER_REPORT_DTS&gt;&lt;WEATHER_TEMP_MAX&gt;35&lt;/WEATHER_TEMP_MAX&gt;&lt;WEATHER_TEMP_MIN&gt;15&lt;/WEATHER_TEMP_MIN&gt;&lt;WEATHER_BAROMETRIC_PRESS&gt;null&lt;/WEATHER_BAROMETRIC_PRESS&gt;&lt;WEATHER_WIND&gt;null&lt;/WEATHER_WIND&gt;&lt;WEATHER_VISIBILITY&gt;null&lt;/WEATHER_VISIBILITY&gt;&lt;WEATHER_REL_HUMIDITY&gt;null&lt;/WEATHER_REL_HUMIDITY&gt;&lt;WEATHER_SUNRISE&gt;0729&lt;/WEATHER_SUNRISE&gt;&lt;WEATHER_SUNSET&gt;1805&lt;/WEATHER_SUNSET&gt;&lt;WEATHER_MOONRISE&gt;2044&lt;/WEATHER_MOONRISE&gt;&lt;WEATHER_MOONSET&gt;0930&lt;/WEATHER_MOONSET&gt;&lt;WEATHER_TIDE_HIGH_WATER&gt;null&lt;/WEATHER_TIDE_HIGH_WATER&gt;&lt;WEATHER_TIDE_LOW_WATER&gt;null&lt;/WEATHER_TIDE_LOW_WATER&gt;&lt;WEATHER_HIGH_WATER&gt;null&lt;/WEATHER_HIGH_WATER&gt;&lt;WEATHER_LOW_WATER&gt;null&lt;/WEATHER_LOW_WATER&gt;&lt;WEATHER_STA_NAME&gt;JIWANI&lt;/WEATHER_STA_NAME&gt;&lt;WEATHER_STA_LAT&gt;25.007&lt;/WEATHER_STA_LAT&gt;&lt;WEATHER_STA_LONG&gt;61.8&lt;/WEATHER_STA_LONG&gt;&lt;CIVIL_TWILIGHT_FROM&gt;0704&lt;/CIVIL_TWILIGHT_FROM&gt;&lt;CIVIL_TWILIGHT_TO&gt;1830&lt;/CIVIL_TWILIGHT_TO&gt;&lt;NAUTICAL_TWILIGHT_FROM&gt;0635&lt;/NAUTICAL_TWILIGHT_FROM&gt;&lt;NAUTICAL_TWILIGHT_TO&gt;1858&lt;/NAUTICAL_TWILIGHT_TO&gt;&lt;ASTRO_TWILIGHT_FROM&gt;0607&lt;/ASTRO_TWILIGHT_FROM&gt;&lt;ASTRO_TWILIGHT_TO&gt;1927&lt;/ASTRO_TWILIGHT_TO&gt;&lt;WEATHER_STATE&gt;SUNNY&lt;/WEATHER_STATE&gt;&lt;SEA_STATE&gt;CALM (GLASSY)&lt;/SEA_STATE&gt;&lt;WIND_TYPE&gt;null&lt;/WIND_TYPE&gt;&lt;CLOUD_TYPE&gt;null&lt;/CLOUD_TYPE&gt;&lt;/Row&gt; &lt;Row&gt;&lt;WEATHER_REPORT_DTS&gt;12/13/2011 0:0:0&lt;/WEATHER_REPORT_DTS&gt;&lt;WEATHER_TEMP_MAX&gt;24&lt;/WEATHER_TEMP_MAX&gt;&lt;WEATHER_TEMP_MIN&gt;10&lt;/WEATHER_TEMP_MIN&gt;&lt;WEATHER_BAROMETRIC_PRESS&gt;null&lt;/WEATHER_BAROMETRIC_PRESS&gt;&lt;WEATHER_WIND&gt;null&lt;/WEATHER_WIND&gt;&lt;WEATHER_VISIBILITY&gt;null&lt;/WEATHER_VISIBILITY&gt;&lt;WEATHER_REL_HUMIDITY&gt;null&lt;/WEATHER_REL_HUMIDITY&gt;&lt;WEATHER_SUNRISE&gt;0723&lt;/WEATHER_SUNRISE&gt;&lt;WEATHER_SUNSET&gt;1758&lt;/WEATHER_SUNSET&gt;&lt;WEATHER_MOONRISE&gt;2037&lt;/WEATHER_MOONRISE&gt;&lt;WEATHER_MOONSET&gt;0924&lt;/WEATHER_MOONSET&gt;&lt;WEATHER_TIDE_HIGH_WATER&gt;null&lt;/WEATHER_TIDE_HIGH_WATER&gt;&lt;WEATHER_TIDE_LOW_WATER&gt;null&lt;/WEATHER_TIDE_LOW_WATER&gt;&lt;WEATHER_HIGH_WATER&gt;null&lt;/WEATHER_HIGH_WATER&gt;&lt;WEATHER_LOW_WATER&gt;null&lt;/WEATHER_LOW_WATER&gt;&lt;WEATHER_STA_NAME&gt;MAKRAN&lt;/WEATHER_STA_NAME&gt;&lt;WEATHER_STA_LAT&gt;25.15&lt;/WEATHER_STA_LAT&gt;&lt;WEATHER_STA_LONG&gt;63.38333333333333333333333333333333333&lt;/WEATHER_STA_LONG&gt;&lt;CIVIL_TWILIGHT_FROM&gt;0657&lt;/CIVIL_TWILIGHT_FROM&gt;&lt;CIVIL_TWILIGHT_TO&gt;1823&lt;/CIVIL_TWILIGHT_TO&gt;&lt;NAUTICAL_TWILIGHT_FROM&gt;0629&lt;/NAUTICAL_TWILIGHT_FROM&gt;&lt;NAUTICAL_TWILIGHT_TO&gt;1852&lt;/NAUTICAL_TWILIGHT_TO&gt;&lt;ASTRO_TWILIGHT_FROM&gt;0601&lt;/ASTRO_TWILIGHT_FROM&gt;&lt;ASTRO_TWILIGHT_TO&gt;1920&lt;/ASTRO_TWILIGHT_TO&gt;&lt;WEATHER_STATE&gt;SUNNY&lt;/WEATHER_STATE&gt;&lt;SEA_STATE&gt;null&lt;/SEA_STATE&gt;&lt;WIND_TYPE&gt;null&lt;/WIND_TYPE&gt;&lt;CLOUD_TYPE&gt;null&lt;/CLOUD_TYPE&gt;&lt;/Row&gt; &lt;Row&gt;&lt;WEATHER_REPORT_DTS&gt;12/15/2011 0:0:0&lt;/WEATHER_REPORT_DTS&gt;&lt;WEATHER_TEMP_MAX&gt;19&lt;/WEATHER_TEMP_MAX&gt;&lt;WEATHER_TEMP_MIN&gt;12&lt;/WEATHER_TEMP_MIN&gt;&lt;WEATHER_BAROMETRIC_PRESS&gt;null&lt;/WEATHER_BAROMETRIC_PRESS&gt;&lt;WEATHER_WIND&gt;null&lt;/WEATHER_WIND&gt;&lt;WEATHER_VISIBILITY&gt;null&lt;/WEATHER_VISIBILITY&gt;&lt;WEATHER_REL_HUMIDITY&gt;null&lt;/WEATHER_REL_HUMIDITY&gt;&lt;WEATHER_SUNRISE&gt;0719&lt;/WEATHER_SUNRISE&gt;&lt;WEATHER_SUNSET&gt;1754&lt;/WEATHER_SUNSET&gt;&lt;WEATHER_MOONRISE&gt;2228&lt;/WEATHER_MOONRISE&gt;&lt;WEATHER_MOONSET&gt;1042&lt;/WEATHER_MOONSET&gt;&lt;WEATHER_TIDE_HIGH_WATER&gt;null&lt;/WEATHER_TIDE_HIGH_WATER&gt;&lt;WEATHER_TIDE_LOW_WATER&gt;null&lt;/WEATHER_TIDE_LOW_WATER&gt;&lt;WEATHER_HIGH_WATER&gt;null&lt;/WEATHER_HIGH_WATER&gt;&lt;WEATHER_LOW_WATER&gt;null&lt;/WEATHER_LOW_WATER&gt;&lt;WEATHER_STA_NAME&gt;JNB (ORMARA)&lt;/WEATHER_STA_NAME&gt;&lt;WEATHER_STA_LAT&gt;25.2&lt;/WEATHER_STA_LAT&gt;&lt;WEATHER_STA_LONG&gt;64.63&lt;/WEATHER_STA_LONG&gt;&lt;CIVIL_TWILIGHT_FROM&gt;0654&lt;/CIVIL_TWILIGHT_FROM&gt;&lt;CIVIL_TWILIGHT_TO&gt;1819&lt;/CIVIL_TWILIGHT_TO&gt;&lt;NAUTICAL_TWILIGHT_FROM&gt;0625&lt;/NAUTICAL_TWILIGHT_FROM&gt;&lt;NAUTICAL_TWILIGHT_TO&gt;1847&lt;/NAUTICAL_TWILIGHT_TO&gt;&lt;ASTRO_TWILIGHT_FROM&gt;0557&lt;/ASTRO_TWILIGHT_FROM&gt;&lt;ASTRO_TWILIGHT_TO&gt;1916&lt;/ASTRO_TWILIGHT_TO&gt;&lt;WEATHER_STATE&gt;SUNNY&lt;/WEATHER_STATE&gt;&lt;SEA_STATE&gt;CALM (GLASSY)&lt;/SEA_STATE&gt;&lt;WIND_TYPE&gt;null&lt;/WIND_TYPE&gt;&lt;CLOUD_TYPE&gt;HIGH CLOUDS&lt;/CLOUD_TYPE&gt;&lt;/Row&gt; &lt;/VW_GIS_WEATHER&gt;</return>
            </ns1:getGisWeatherResponse>

            </SOAP-ENV:Body>
            </SOAP-ENV:Envelope>


            ===

            moreover please guide me how can I query it to get data in Relation form from the above xml.

            Looking for positive response from you.

            Regards
            • 3. Re: SOAP XML tag Query in SQL
              odie_63
              My database version is 10.2.0.1.0

              You are right, My xml is given below from the IE (View source option);
              OK, thanks for the details.
              moreover please guide me how can I query it to get data in Relation form from the above xml.
              Did you go through the link I gave above? It deals with a very similar situation.
              • 4. Re: SOAP XML tag Query in SQL
                MuhabbatAli
                Thanks for the reponse from you.

                Yes I have gone through that.
                But I want to access only xml for "<VW_GIS_WEATHER>" tag in Query. because My actual user data start from here.

                I have done it but i have to store it in temp_table , remove unwanted xml tags , replace "&lt;" and "&gt;" charanters '<' and '/>' and then finally make XML query from table.

                My requirement how can I directly query my above mentioned tags (with inner tags) directly without invloving Temp_table.
                Please guide my To develop XQuery to get Only required data.


                Looking for positive response from you.

                Regards
                • 5. Re: SOAP XML tag Query in SQL
                  odie_63
                  See if this works for you :
                  select r.*
                  from tmp_xml t
                     , xmltable(
                         xmlnamespaces(
                           'http://schemas.xmlsoap.org/soap/envelope/' as "env"
                         , 'http://oracle.j2ee.myservice/MyWebService.wsdl' as "ns1"
                         )
                       , '/env:Envelope/env:Body/ns1:getGisWeatherResponse'
                         passing t.object_value
                         columns xmlbody xmltype path 'return/text()'
                       ) x
                     , xmltable(
                         '/VW_GIS_WEATHER/Row'
                         passing xmltype(dbms_xmlgen.convert(x.xmlbody.getclobval(), 1))
                         columns WEATHER_REPORT_DTS varchar2(30) path 'WEATHER_REPORT_DTS'
                               , WEATHER_TEMP_MAX   varchar2(30) path 'WEATHER_TEMP_MAX'
                               , WEATHER_TEMP_MIN   varchar2(30) path 'WEATHER_TEMP_MIN'
                               , WEATHER_BAROMETRIC_PRESS  varchar2(30) path 'WEATHER_BAROMETRIC_PRESS'
                       ) r
                  ;
                  
                   
                  WEATHER_REPORT_DTS             WEATHER_TEMP_MAX               WEATHER_TEMP_MIN               WEATHER_BAROMETRIC_PRESS
                  ------------------------------ ------------------------------ ------------------------------ ------------------------------
                  12/15/2011 11:57:0             32                             20                             null
                  2/24/2011 11:8:0               null                           null                           null
                  10/4/2011 14:34:0              null                           null                           null
                  12/14/2011 0:0:0               25                             12                             100
                  12/12/2011 0:0:0               30                             12                             null
                  12/13/2011 0:0:0               35                             15                             null
                  12/13/2011 0:0:0               24                             10                             null
                  12/15/2011 0:0:0               19                             12                             null
                   
                  8 rows selected
                   
                  TMP_XML is an XMLType table where I stored your sample file :
                  CREATE TABLE tmp_xml OF XMLType;
                  • 6. Re: SOAP XML tag Query in SQL
                    MuhabbatAli
                    Thanks for the respose. I ran the Query It was giving me error (Parsing XML).
                    Then I run only this part of the Query

                    select x.*
                    from tmp_xml t
                    , xmltable(
                    xmlnamespaces(
                    'http://schemas.xmlsoap.org/soap/envelope/' as "env"
                    , 'http://oracle.j2ee.myservice/MyWebService.wsdl' as "ns1"
                    )
                    , '/env:Envelope/env:Body/ns1:getGisWeatherResponse'
                    passing t.object_value
                    columns xmlbody xmltype path 'return/text()'
                    ) x


                    It gives me exact result that I need. But it contains '&gt;' and '&lt;' characers instead of '<' a and '/>' characters.

                    One Solution is to store the above mentioned Query Result into CLOB variable and iterate in Loop to replace '&gt;' and '&lt;' characers with '<' a and '/>'
                    Is there any way that I can replace '&gt;' and '&lt;' characers with '<' a and '/>' WITHOUT iterating throuh Loop.


                    I think we are close to solution.

                    Regards
                    • 7. Re: SOAP XML tag Query in SQL
                      odie_63
                      I ran the Query It was giving me error (Parsing XML).
                      Please give the complete error message.
                      Is there any way that I can replace '&amp;gt;' and '&amp;lt;' characers with '<' a and '/>' WITHOUT iterating throuh Loop.
                      I already showed you how to do that : use DBMS_XMLGEN.CONVERT.
                      In the previous query I used it to unescape the XML payload and pass it directly to another XMLTable within the same statement :
                      passing xmltype(dbms_xmlgen.convert(x.xmlbody.getclobval(), 1))
                      Apparently, it doesn't work for you for some reasons.
                      If you want to try a two-step approach, first extract the embedded document as CLOB, then convert it to XMLType and pass it to a second XMLTable :
                      select dbms_xmlgen.convert(x.xmlbody.getclobval(), 1)
                      into v_xmlbody
                      from tmp_xml t
                         , xmltable(
                             xmlnamespaces(
                               'http://schemas.xmlsoap.org/soap/envelope/' as "env"
                             , 'http://oracle.j2ee.myservice/MyWebService.wsdl' as "ns1"
                             )
                           , '/env:Envelope/env:Body/ns1:getGisWeatherResponse'
                             passing t.object_value
                             columns xmlbody xmltype path 'return/text()'
                           ) x
                      ;
                      select r.*
                      from tmp_xml t
                         , xmltable(
                             '/VW_GIS_WEATHER/Row'
                             passing xmltype(v_xmlbody)
                             columns WEATHER_REPORT_DTS varchar2(30) path 'WEATHER_REPORT_DTS'
                                   , WEATHER_TEMP_MAX   varchar2(30) path 'WEATHER_TEMP_MAX'
                                   , WEATHER_TEMP_MIN   varchar2(30) path 'WEATHER_TEMP_MIN'
                                   , WEATHER_BAROMETRIC_PRESS  varchar2(30) path 'WEATHER_BAROMETRIC_PRESS'
                           ) r
                      ;
                      • 8. Re: SOAP XML tag Query in SQL
                        MuhabbatAli
                        My problem is resolved with the help of your Guideline.

                        Thanks for the reponse .


                        Regards