Home » Developer & Programmer » JDeveloper, Java & XML » Issues while retriving data from xmltype (oracle 11g )
Issues while retriving data from xmltype [message #475174] Mon, 13 September 2010 07:59 Go to next message
just.rahul
Messages: 9
Registered: November 2007
Junior Member
I have created a table as following
CREATE TABLE hl9(
col number,
col3 XMLTYPE);

Inserted a xml in col3 column
xml is as following
<section>
<code code="27515-6" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC" displayName="PRIMARY DIAGNOSIS "/>
<title>PRIMARY DIAGNOSIS</title>
<text>
<paragraph ID="content-1">
<content ID="content-2">bipolar affective disorder</content>
</paragraph>
</text>
<entry>
<observation classCode="OBS" moodCode="EVN">
HL7 Additional Information Specification Implementation Guide CDAR2AIS0000R030
Copyright © 1998-2007 Health Level Seven, Inc. All rights reserved.
Release 3.0 Draft Standard
Page 15
March 2007
<code code="27515-6" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC" displayName="PRIMARY DIAGNOSIS"/>
<text>
<reference value="#content-1"/>
</text>
<effectiveTime value="20030326"/>
<value code="296.7" codeSystem="2.16.840.1.113883.6.103" codeSystemName="ICD-
9-CM" displayName="BIPOLAR AFFECTIVE DISORDER" xsi:type="CD">
<originalText>
<reference value="#content-2"/>
</originalText>
</value>
</observation>
</entry>
</section>

now i have run 2 queries
SQL> select extractvalue(col3, '/section/entry/observation/effectiveTime') label3 from hl9;

LABEL3
-----------------------------------------------------------------


SQL> select extractvalue(col3, '/section/title') label3 from hl9;

LABEL3
-----------------------------------------------------------------PRIMARY DIAGNOSIS

please let me know how to get the value of effectiveTime tag
Re: Issues while retriving data from xmltype [message #475178 is a reply to message #475174] Mon, 13 September 2010 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (
  2  select xmltype(
  3  '<section>
  4  <code code="27515-6" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC" 
  5  displayName="PRIMARY DIAGNOSIS "/>
  6  <title>PRIMARY DIAGNOSIS</title>
  7  <text>
  8  <paragraph ID="content-1">
  9  <content ID="content-2">bipolar affective disorder</content>
 10  </paragraph>
 11  </text>
 12  <entry>
 13  <observation classCode="OBS" moodCode="EVN">
 14  HL7 Additional Information Specification Implementation Guide CDAR2AIS0000R030
 15  Copyright © 1998-2007 Health Level Seven, Inc. All rights reserved.
 16  Release 3.0 Draft Standard
 17  Page 15
 18  March 2007
 19  <code code="27515-6" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC" 
 20  displayName="PRIMARY DIAGNOSIS"/>
 21  <text>
 22  <reference value="#content-1"/>
 23  </text>
 24  <effectiveTime value="20030326"/>
 25  <value code="296.7" codeSystem="2.16.840.1.113883.6.103" codeSystemName="ICD-9-CM" 
 26  displayName="BIPOLAR AFFECTIVE DISORDER">
 27  <originalText>
 28  <reference value="#content-2"/>
 29  </originalText>
 30  </value>
 31  </observation>
 32  </entry>
 33  </section>') val from dual
 34  )
 35  select extractvalue(value(x), '//effectiveTime/@value') effectTime
 36  from data, table(xmlsequence(extract(val, '//effectiveTime'))) x
 37  /
EFFECTTIME
----------------------------------------------------------------------------------------
20030326

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Issues while retriving data from xmltype [message #475181 is a reply to message #475178] Mon, 13 September 2010 08:59 Go to previous messageGo to next message
just.rahul
Messages: 9
Registered: November 2007
Junior Member
Hi Michel,

Thanks for your reply. You have given the example using the xml in query in my case the data is in the column col3 of table hl9.
when i am selecting as below
SQL> with data as (
2 select col3 from hl9
3 )
4 select extractvalue(value(x), '//effectiveTime/@value') effectTime
5 from data, table(xmlsequence(extract(col3, '//effectiveTime'))) x
6 /

no rows selected

i am getting no rows selected
Re: Issues while retriving data from xmltype [message #475186 is a reply to message #475181] Mon, 13 September 2010 09:16 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So there is no row in your table.

Regards
Michel
Previous Topic: delete a node from xml in oracle
Next Topic: Passing array structure from java callable stmnt to a pl/sql stored procedure
Goto Forum:
  


Current Time: Thu Mar 28 12:44:25 CDT 2024