Home » Developer & Programmer » JDeveloper, Java & XML » updating CLOB value (oracle 10.2.0.3)
updating CLOB value [message #536075] Mon, 19 December 2011 03:15 Go to next message
deepa_balu
Messages: 74
Registered: March 2005
Member
Hi,

I have the following xml in a CLOB Field


<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<TuningDescription xmlns="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningDefinition">
    <tuningSession>
        <TuningSessionProperties creationDate="2011-11-09+05:30" lastUpdate="2011-11-09+05:30" 
owner="osmadmin" recommendedDataOfApplication="10/11/2011" sourceTuningSessionId="TS_1" tempTuningSessionId="TS_1_TMP_1" tuningSessionName="TS1" 
tuningSessionOverview="Parameter Tuning Modified:CELL3G: 1, 
" tuningSessionPurpose="" workingZoneId="51343" workingZoneLabel="500"/>
        <tuningOperationList>
            <ns1:parameterTuningOperationList xmlns:ns1="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningTaskDefinition">
                <ns2:parameterTuningOperationType objectType="CELL3G" xmlns:ns2="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningOperationDefinition">
                    <ns2:source objectId="3230" objectType="CELL3G">
                        <ns2:parameterDescription originValue="User Defined" parameterName="PARAM_UMTS_4943" updateType="CREATE">
                            <ns2:serverAttributeValues attributeName="FOR_VALUE" attributeValue="4200"/>
                            <ns2:serverAttributeValues attributeName="OP_VALUE" attributeValue="NaN"/>
                        </ns2:parameterDescription>
                    </ns2:source>
                </ns2:parameterTuningOperationType>
            </ns1:parameterTuningOperationList>
        </tuningOperationList>
    </tuningSession>
</TuningDescription>


I have to look for objectId Tag in the xml and
Here i want to replace objectId with another Mapping value from a mapping table
Below is the mapping table.

create table ODMAP(OID number,mapID number);
insert into ODMAP values(3230,7000);
insert into ODMAP values(3231,7001);
insert into ODMAP values(3232,7002);


Pls help as i am not good in xml extraction.

[Updated on: Mon, 19 December 2011 04:24] by Moderator

Report message to a moderator

Re: updating CLOB value [message #536080 is a reply to message #536075] Mon, 19 December 2011 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
http://www.orafaq.com/forum/mv/msg/176281/530551/102589/#msg_530551

Regards
Michel

Report message to a moderator

Re: updating CLOB value [message #536091 is a reply to message #536080] Mon, 19 December 2011 03:39 Go to previous messageGo to next message
deepa_balu
Messages: 74
Registered: March 2005
Member
Yes i had posted same query before.But now i am not able to parse that xml..So i am need of help

I tried the below and its not working.

select extractvalue(xmltype(a),
'/TuningDescription/tuningSession/tuningOperationList/ns1:parameterTuningOperationList/ns2:parameterTuningOperationType/source/@objec tId')
from test

Report message to a moderator

Re: updating CLOB value [message #536105 is a reply to message #536091] Mon, 19 December 2011 04:03 Go to previous messageGo to next message
deepa_balu
Messages: 74
Registered: March 2005
Member
Thnks i figured out


select extractvalue(xmltype(tunercontent),
'/TuningDescription/tuningSession/tuningOperationList//ns1:parameterTuningOperationList//ns2:parameterTuningOperationType//ns2:source /@objectId',
'xmlns= "http://www.alcatel.com/2005/MUSE/Tuning/maatTuningDefinition" xmlns:ns1="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningTaskDefinition" xmlns:ns2="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningOperatiohttp://www.orafaq.com/forum/r/frm_id/20/67190/nDefinition"')
from test


is working

[Updated on: Mon, 19 December 2011 04:23] by Moderator

Report message to a moderator

Re: updating CLOB value [message #536108 is a reply to message #536105] Mon, 19 December 2011 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thank for the feedback.

Regards
Michel

Report message to a moderator

Re: updating CLOB value [message #536140 is a reply to message #536108] Mon, 19 December 2011 07:54 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member
Another way of doing it..


with rec as 
(Select XMLTYPE('<TuningDescription xmlns="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningDefinition">
    <tuningSession>
        <TuningSessionProperties creationDate="2011-11-09+05:30" lastUpdate="2011-11-09+05:30" owner="osmadmin" recommendedDataOfApplication="10/11/2011" sourceTuningSessionId="TS_1" tempTuningSessionId="TS_1_TMP_1" tuningSessionName="TS1" tuningSessionOverview="Parameter Tuning Modified:CELL3G: 1, " tuningSessionPurpose="" workingZoneId="51343" workingZoneLabel="500"/>
        <tuningOperationList>
            <ns1:parameterTuningOperationList xmlns:ns1="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningTaskDefinition">
                <ns2:parameterTuningOperationType objectType="CELL3G" xmlns:ns2="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningOperationDefinition">
                    <ns2:source objectId="3230" objectType="CELL3G">
                        <ns2:parameterDescription originValue="User Defined" parameterName="PARAM_UMTS_4943" updateType="CREATE">
                            <ns2:serverAttributeValues attributeName="FOR_VALUE" attributeValue="4200"/>
                            <ns2:serverAttributeValues attributeName="OP_VALUE" attributeValue="NaN"/>
                        </ns2:parameterDescription>
                    </ns2:source>
                </ns2:parameterTuningOperationType>
            </ns1:parameterTuningOperationList>
        </tuningOperationList>
    </tuningSession>
</TuningDescription>')x
FROM DUAL ) 
SELECT extractvalue(x,'/TuningDescription/tuningSession/tuningOperationList/ns1:parameterTuningOperationList/ns2:parameterTuningOperationType/ns2:source/@objectId',
'xmlns="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningDefinition" xmlns:ns1="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningTaskDefinition" xmlns:ns2="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningOperationDefinition" ') objid
from rec

[Updated on: Mon, 19 December 2011 07:55]

Report message to a moderator

Re: updating CLOB value [message #536238 is a reply to message #536140] Mon, 19 December 2011 23:25 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member
Ooops.. I pasted the same logic used by the OP. Anyway here is another version using XMLTABLE. 

SELECT OBJID 
  FROM XMLTABLE(XMLNamespaces(default 'http://www.alcatel.com/2005/MUSE/Tuning/maatTuningDefinition',
  'http://www.alcatel.com/2005/MUSE/Tuning/maatTuningTaskDefinition' as "adn1",
  'http://www.alcatel.com/2005/MUSE/Tuning/maatTuningOperationDefinition' as "adn2" ), 
  '/TuningDescription/tuningSession/tuningOperationList/adn1:parameterTuningOperationList'
PASSING XMLTYPE('<TuningDescription xmlns="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningDefinition">
    <tuningSession>
        <TuningSessionProperties creationDate="2011-11-09+05:30" lastUpdate="2011-11-09+05:30" owner="osmadmin" recommendedDataOfApplication="10/11/2011" sourceTuningSessionId="TS_1" tempTuningSessionId="TS_1_TMP_1" tuningSessionName="TS1" tuningSessionOverview="Parameter Tuning Modified:CELL3G: 1, " tuningSessionPurpose="" workingZoneId="51343" workingZoneLabel="500"/>
        <tuningOperationList>
            <ns1:parameterTuningOperationList xmlns:ns1="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningTaskDefinition">
                <ns2:parameterTuningOperationType objectType="CELL3G" xmlns:ns2="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningOperationDefinition">
                    <ns2:source objectId="3230" objectType="CELL3G">
                        <ns2:parameterDescription originValue="User Defined" parameterName="PARAM_UMTS_4943" updateType="CREATE">
                            <ns2:serverAttributeValues attributeName="FOR_VALUE" attributeValue="4200"/>
                            <ns2:serverAttributeValues attributeName="OP_VALUE" attributeValue="NaN"/>
                        </ns2:parameterDescription>
                    </ns2:source>
                </ns2:parameterTuningOperationType>
            </ns1:parameterTuningOperationList>
        </tuningOperationList>
    </tuningSession>
</TuningDescription>') 
                COLUMNS
                OBJID   VARCHAR2(30)  PATH '/adn1:parameterTuningOperationList/adn2:parameterTuningOperationType/adn2:source/@objectId');

Report message to a moderator

Previous Topic: how to load file 'oracle weblogic server'
Next Topic: ora 29531 Why?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jan 26 00:57:20 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.