| SELECT XML DATA IN CLOB [message #322330] |
Fri, 23 May 2008 02:14  |
zuntoo
Messages: 11
Registered: March 2007
|
Junior Member |
|
|
Hi guys & GURUS,
I am experiencing problem selecting XML DATA stored in CLOB datatype. The point here is that i am unable to select the exact value of any field like for ex: Order Id
Now i need to write complex queries to retrieve these values and most of the times i am not getting 100 result using SUBSTR OR INSTR, as the position of the value (Order Id or any other value like ServiceId) Keeps on changing.
Kindly suggest any solution which can help me out selecting exact values from this XML formated data.
GIVE below is XML FORMAT as sample.
<?xml version="1.0" encoding="UTF-8"?>
<EAIServiceOrderProvisioning>
<EAIOrderEntryOrders>
<Type>OOSS</Type>
<OrderEntryOrders>
<AccountIntegrationId>4744181</AccountIntegrationId>
<New_Package_Plan>Basic Prepaid C</New_Package_Plan>
<Old_Package_Plan>NewBasic</Old_Package_Plan>
<OrderId>1-1I2BJ0T</OrderId>
<Order_OrderTypeId>10</Order_OrderTypeId>
<Order_ACSBillingAccountIntegId/>
<SOPriority>2</SOPriority>
<MNPFlag/>
<MNPOrder/>
<ListOfOrderEntryLineItems>
<OrderEntryLineItems>
<LineItem_ActionCode>Update</LineItem_ActionCode>
<AgreementId/>
<AssetIntegrationId>1-1Y1R-986</AssetIntegrationId>
<BusinessLine/>
<OrderHeaderId>1-1I2BJ0T</OrderHeaderId>
<LineItem_OrderTypeId>10</LineItem_OrderTypeId>
<PartNumber>S-10</PartNumber>
<ACSEndDate/>
<ACSStartDate>22/05/2008 12:03:12</ACSStartDate>
<ServiceId>03459331747</ServiceId>
<Flag>Y</Flag>
The table structure is :
TABLE1
id number,
xmldata clob,
|
|
|
|
|
|
|
|
|
|
| Re: SELECT XML DATA IN CLOB [message #323476 is a reply to message #322415] |
Wed, 28 May 2008 09:47  |
zuntoo
Messages: 11
Registered: March 2007
|
Junior Member |
|
|
That is actually what i know,
well where I am stuck is, when i try to execute this query it returns an error
ORA-19025: EXTRACTVALUE returns value of only one node.
select extractValue(XMLType.createxml(
'<?xml version="1.0" encoding="UTF-8"?>
<EAIServiceOrderProvisioning>
<EAIOrderEntryOrders>
<Type>OOSS</Type>
<OrderEntryOrders>
<AccountIntegrationId>56048008</AccountIntegrationId>
<New_Package_Plan>Q-Brand</New_Package_Plan>
<Old_Package_Plan>Q-Brand</Old_Package_Plan>
<OrderId>1-1GBE0DB</OrderId>
<Order_OrderTypeId>02</Order_OrderTypeId>
<Order_ACSBillingAccountIntegId/>
<SOPriority/>
<MNPFlag/>
<MNPOrder/>
<ListOfOrderEntryLineItems>
<OrderEntryLineItems>
<LineItem_ActionCode>Update</LineItem_ActionCode>
<AgreementId/>
<AssetIntegrationId>1-2DRAD-61</AssetIntegrationId>
<BusinessLine/>
<OrderHeaderId>1-1GBE0DB</OrderHeaderId>
<LineItem_OrderTypeId>02</LineItem_OrderTypeId>
<PartNumber>S-10</PartNumber>
<ACSEndDate/>
<ACSStartDate>22/05/2008 16:34:31</ACSStartDate>
<ServiceId>03432575918</ServiceId>
<Flag>Y</Flag>
<Status>Open</Status>
<XParentAssetIntegId>1-2DRAD-61</XParentAssetIntegId>
<XRootAssetIntegId>1-2DRAD-61</XRootAssetIntegId>
<ACSJoinedAccountIntegId>56048008</ACSJoinedAccountIntegId>
<ACSBillingAccountIntegId/>
<ACSResourceId>1-1GBE0DB</ACSResourceId>
<ACSOldResourceId>1-1GBE0DB</ACSOldResourceId>
<ListOfActivityPlan/>
<ListOfOrderItemXa>
<OrderItemXa>
<ActionCode>-</ActionCode>
<IntegrationId/>
<Name>ADM1</Name>
<Value>41044734</Value>
<ListOfACSOossNumbers/>
</OrderItemXa>'),
'/EAIServiceOrderProvisioning/EAIOrderEntryOrders/OrderEntryOrders/
ListOfOrderEntryLineItems/OrderEntryLineItems/ListOfOrderItemXa/OrderItemXa/Name')
from dual
With Thanks.
[Updated on: Thu, 29 May 2008 05:46] Report message to a moderator |
|
|
|
|
|
|
|
| Re: SELECT XML DATA IN CLOB [message #324863 is a reply to message #322330] |
Wed, 04 June 2008 06:33 |
zuntoo
Messages: 11
Registered: March 2007
|
Junior Member |
|
|
[quote title=zuntoo wrote on Fri, 23 May 2008 02:14]Hi guys & GURUS,
I am experiencing problem selecting XML DATA stored in CLOB datatype. The point here is that i am unable to select the
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
