 Urgent:-XML Query [message #111461] |
Wed, 16 March 2005 09:25  |
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
|
Member |
|
|
Hello Experts,
I am new to XML and I have written the query like this which give me the output I have mentioned below.
Query:-
SELECT extract(DI.di_info, '//Juris/text()') .getStringVal()
from ARV_DI DI
where DI.DI_TYPE_CD = 11004 and
DI.DI_ID in (8457356, 8457357, 8457358, 8457359)
Output:-44454647484950
But I want the output row by row i.e.:-
44
45
46
47
48
49
50
I dont know how to extract the data in this way.Pls. help me as I am new to XML and couldnt find a way out after struggling for two days.
My data Structure in DI_INFO column is like this :-
DI xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.cch-lis.com/di.xsd">
<DIId>8457359</DIId>
<DIType>SOP</DIType>
<IsConditional>true</IsConditional>
<Created>2004-05-08T03:50:40</Created>
<LastModifiedDate>2005-03-15T21:01:42.000000</LastModifiedDate>
<LastModifiedBy>1911</LastModifiedBy>
<Instructions>
<Instruction>
<Tag>3B581660C2BB409CB7E06B9E0FBD0AAC</Tag>
<Conditions>
<SuitTypeCond>
<SuitTypes>
<SuitType>35001</SuitType>
</SuitTypes>
</SuitTypeCond>
</Conditions>
<Actions>
<Action>
<Tag>D89AAB3D317A46E3929CB1A12D39BFD9</Tag>
<ActionType>DeliverPapers</ActionType>
<What>SOPPapers</What>
<How>FedEx</How>
<Who>814283</Who>
<FedExService>FedEx2Day</FedExService>
</Action>
</Actions>
</Instruction>
<Instruction>
<Tag>FA22906CE72143DDA7ADD6591B5F861E</Tag>
<Conditions>
<SuitTypeCond>
<SuitTypes>
<SuitType>35003</SuitType>
</SuitTypes>
</SuitTypeCond>
<JurisdictionCond>
<Jurisdictions>
]<Juris>44</Juris>
<Juris>45</Juris>
<Juris>46</Juris>
<Juris>47</Juris>
<Juris>48</Juris>
<Juris>49</Juris>
<Juris>50</Juris[/B]> </Jurisdictions>
</JurisdictionCond>
</Conditions>
<Actions>
<Action>
<Tag>C2E892A22624439981689774C68B9EC2</Tag>
<ActionType>DeliverPapers</ActionType>
<What>SOPPapers</What>
<How>FedEx</How>
<Who>8888</Who>
<FedExService>FedEx2Day</FedExService>
</Action>
</Actions>
</Instruction>
</Instructions>
</DI>
Oracle version 9.2.0.5. O.S. is WinXp.
Pls. help me as this is urgent.
Thanks in Advance.
Milind.
|
|
|
|
| Re: Urgent:-XML Query [message #111569 is a reply to message #111461] |
Thu, 17 March 2005 09:48  |
Rans
Messages: 11
Registered: February 2005
Location: chennai
|
Junior Member |
|
|
Try ExtractValue and XMLSEQUENCE table combination probably that would fetch the result in the manner you specified..
Something like this in Oracle 9i 2..
select extractvalue(value(x),'//simplechildelement') from XMLTABLE D,
TABLE(XMLSEQUENCE(extract(value(D),'/ROOTELEMENT/NEXTELEMENT')))X;
Child element is the simple XML NODE the content of which you want to fetch and ROOTELEMENT and NEXTELEMENT are its parent and grand parent(whatever you call) nodes...
try it..
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
