Home » Developer & Programmer » JDeveloper, Java & XML » Select data from XMLType column (11G)
Select data from XMLType column [message #575618] Thu, 24 January 2013 15:29 Go to next message
sqlsatya
Messages: 10
Registered: December 2012
Junior Member
I have a table Table_xml_tmp which has a column MESSAGECONTENTS as XMLType data which contains data as follows

"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xclimSchemaMain xmlns="http://clones.telcordia.com">
    <response>
        <clHeader>
            <msgName>queryPlace</msgName>
            <object>CLLICode</object>
            <msgType>RESPONSE</msgType>
            <sender>CLONES</sender>
            <senderInstance>[url]http://directcodecenter.commonlanguage.com[/url]</senderInstance>
            <operationStatus>SUCCESS</operationStatus>
            <correlationId>1358984262589clon2861349949B3920BB9A397201387788AD50E295733560</correlationId>
            <additionalInfo>
                <returnStatus>
                    <return Code>Success</returnCode>
                </returnStatus>
            </additionalInfo>
        </clHeader>
        <clData>
            <list>
                <searchString>queryPlace for CHAMBLEE in GA, USA</searchString>
                <searchScope></searchScope>
                <stateName>GEORGIA</stateName>
                <countryCode>USA</countryCode>
                <siteInfo>
                    <clliCode>CHMBGA</clliCode>
                    <status>A</status>
                    <placeName>CHAMBLEE</placeName>
                    <normalizedPlaceName>CHAMBLEE</normalizedPlaceName>
                    <placeType>CITY</placeType>
                    <countyName>DEKALB</countyName>
                    <lata>438</lata>
                </siteInfo>
            </list>
        </clData>
    </response>
</xclimSchemaMain>
"


I need to get all rows where node <operationStatus> value as SUCCESS
I am running following query but it is returning null value insted of data of columns

SELECT a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()
FROM Table_xml_tmp a
WHERE a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()= 'SUCCESS'


Please suggest what changes required


[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Thu, 24 January 2013 17:46] by Moderator

Report message to a moderator

Re: Select data from XMLType column [message #575623 is a reply to message #575618] Thu, 24 January 2013 17:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11gR2> create table table_xml_tmp
  2    (messagecontents  xmltype)
  3  /

Table created.

SCOTT@orcl_11gR2> insert into table_xml_tmp values (xmltype (
  2  '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  3   <xclimSchemaMain xmlns="http://clones.telcordia.com">
  4  	 <response>
  5  	     <clHeader>
  6  		 <msgName>queryPlace</msgName>
  7  		 <object>CLLICode</object>
  8  		 <msgType>RESPONSE</msgType>
  9  		 <sender>CLONES</sender>
 10  		 <senderInstance>[url]http://directcodecenter.commonlanguage.com[/url]</senderInstance>
 11  		 <operationStatus>SUCCESS</operationStatus>
 12  		 <correlationId>1358984262589clon2861349949B3920BB9A397201387788AD50E295733560</correlationId>
 13  		 <additionalInfo>
 14  		     <returnStatus>
 15  			 <returnCode>Success</returnCode>
 16  		     </returnStatus>
 17  		 </additionalInfo>
 18  	     </clHeader>
 19  	     <clData>
 20  		 <list>
 21  		     <searchString>queryPlace for CHAMBLEE in GA, USA</searchString>
 22  		     <searchScope></searchScope>
 23  		     <stateName>GEORGIA</stateName>
 24  		     <countryCode>USA</countryCode>
 25  		     <siteInfo>
 26  			 <clliCode>CHMBGA</clliCode>
 27  			 <status>A</status>
 28  			 <placeName>CHAMBLEE</placeName>
 29  			 <normalizedPlaceName>CHAMBLEE</normalizedPlaceName>
 30  			 <placeType>CITY</placeType>
 31  			 <countyName>DEKALB</countyName>
 32  			 <lata>438</lata>
 33  		     </siteInfo>
 34  		 </list>
 35  	     </clData>
 36  	 </response>
 37  </xclimSchemaMain>'
 38  ))
 39  /

1 row created.

SCOTT@orcl_11gR2> SELECT a.MESSAGECONTENTS.extract
  2  	      ('//clHeader/operationStatus/text()',
  3  	       'xmlns="http://clones.telcordia.com"').getStringVal()
  4  FROM   Table_xml_tmp a
  5  WHERE  a.MESSAGECONTENTS.extract
  6  	      ('//clHeader/operationStatus/text()',
  7  	       'xmlns="http://clones.telcordia.com"').getStringVal()= 'SUCCESS'
  8  /

A.MESSAGECONTENTS.EXTRACT('//CLHEADER/OPERATIONSTATUS/TEXT()','XMLNS="HTTP://CLO
--------------------------------------------------------------------------------
SUCCESS

1 row selected.


Re: Select data from XMLType column [message #575775 is a reply to message #575623] Sun, 27 January 2013 08:09 Go to previous message
sqlsatya
Messages: 10
Registered: December 2012
Junior Member
Thank you Barbara, it is working now!!
Previous Topic: XML parsing help
Next Topic: XML generation at run time!!
Goto Forum:
  


Current Time: Thu Mar 28 04:30:41 CDT 2024