Home » Developer & Programmer » JDeveloper, Java & XML » SELECT XML DATA IN CLOB (10g, Windows.)
SELECT XML DATA IN CLOB [message #322330] Fri, 23 May 2008 02:14 Go to next message
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 #322367 is a reply to message #322330] Fri, 23 May 2008 04:53 Go to previous messageGo to next message
boukerker
Messages: 11
Registered: January 2008
Location: Paris
Junior Member
use XMLType, to create it XMLType.createXML(xmldata) and you can do query using XML funcion
Regards
Re: SELECT XML DATA IN CLOB [message #322412 is a reply to message #322367] Fri, 23 May 2008 07:19 Go to previous messageGo to next message
zuntoo
Messages: 11
Registered: March 2007
Junior Member
would u like to explain a bit with some examples.
with thanks.
Re: SELECT XML DATA IN CLOB [message #322415 is a reply to message #322412] Fri, 23 May 2008 07:46 Go to previous messageGo to next message
boukerker
Messages: 11
Registered: January 2008
Location: Paris
Junior Member
Example to extract a Node Value
SELECT extractValue(XMLType.createXML('<TEST> Value </TEST>'), 'TEST') FROM dual ;
Re: SELECT XML DATA IN CLOB [message #323476 is a reply to message #322415] Wed, 28 May 2008 09:47 Go to previous messageGo to next message
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 #323723 is a reply to message #322330] Thu, 29 May 2008 05:53 Go to previous messageGo to next message
zuntoo
Messages: 11
Registered: March 2007
Junior Member
IS THERE ANYONE ON THIS FORUM WHO CAN ANSWER THIS.........
Re: SELECT XML DATA IN CLOB [message #323736 is a reply to message #323723] Thu, 29 May 2008 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to SHOUT.

There are many examples of the use of EXTRACTVALUE and the like in this forum, put it in search field and try to see if you find the answer.

Regards
Michel
Re: SELECT XML DATA IN CLOB [message #324863 is a reply to message #322330] Wed, 04 June 2008 06:33 Go to previous messageGo to next message
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
Re: SELECT XML DATA IN CLOB [message #324880 is a reply to message #324863] Wed, 04 June 2008 07:21 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm very eager to know the end...

Regards
Michel
Previous Topic: Retrieving XML data type column in PL/SQL cursor
Next Topic: XMLTYPE Record is not getting update
Goto Forum:
  


Current Time: Fri Mar 29 08:24:37 CDT 2024