| Issues while retriving data from xmltype [message #475174] |
Mon, 13 September 2010 07:59  |
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  |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
