OraFAQ Forum: JDeveloper, Java & XML » Select data from XMLType column

Home » Developer & Programmer » JDeveloper, Java & XML » Select data from XMLType column (11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select data from XMLType column [message #575618]

Thu, 24 January 2013 15:29

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

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

Re: Select data from XMLType column [message #575775 is a reply to message #575623]

Sun, 27 January 2013 08:09

sqlsatya
Messages: 10
Registered: December 2012

Junior Member

Thank you Barbara, it is working now!!

Report message to a moderator

Previous Topic:	XML parsing help
Next Topic:	XML generation at run time!!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 17:45:08 CST 2025