OraFAQ Forum: JDeveloper, Java & XML » Select Data from XMLTYPE (9.2.0.1)

Home » Developer & Programmer » JDeveloper, Java & XML » Select Data from XMLTYPE (9.2.0.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select Data from XMLTYPE (9.2.0.1) [message #126884]

Thu, 07 July 2005 02:19

oldie74
Messages: 2
Registered: July 2005
Location: Germany

Junior Member

Hello!

I have an XML-File with the following structure. The XML-Data is stored into an XMLTYPE-Tablecolumn.

<DATA>
<Common>
<Field>
<Label>Begin Date </Label>
<Value Name="BEG_DATE">02.05.2006</Value>
</Field>
<Field>
<Label>End Date</Label>
<Value Name="END_DATE">01.06.2016</Value>
</Field>
<Field>
<Label>type</Label>
<Value Name="TYPE">Example</Value>
</Field>
</Common>

<Customer>
<Field>
<Label>Name</Label>
<Value Name="CUST_NAME">Smith</Value>
</Field>
<Field>
<Label>Date of birth</Label>
<Value Name="CUST_BIRTH_DATE">27.08.1972</Value>
</Field>
</Customer>
</DATA>

What I want to do is to update a specific value. For example "END_DATE" to "02.06.2017".

I tried the following SQL but it always updates all Value-Nodes:

UPDATE XML_TAB xt
SET xt.xml = updateXML(xt.XML,'/DATA/Common/Field/Value/text()','02.06.2017')
WHERE xt.ID = :update_id
AND existsNode(xt.XML,'/DATA/Common/Field/Value[@Name="END_DATE"]') = 1

Thanks for your help!

Olli

Report message to a moderator

Re: Select Data from XMLTYPE (9.2.0.1) [message #126928 is a reply to message #126884]

Thu, 07 July 2005 07:32

Frank Naude
Messages: 4579
Registered: April 1998

Senior Member

Try something like this:

UPDATE xml_tab
SET xml = updateXML(xml, '/DATA/Common/Field/Value[@Name="END_DATE"]/text()', '02.06.2017')
WHERE id = :update_id;

Best regards.

Frank

Report message to a moderator

Re: Select Data from XMLTYPE (9.2.0.1) [message #126937 is a reply to message #126928]

Thu, 07 July 2005 07:56

oldie74
Messages: 2
Registered: July 2005
Location: Germany

Junior Member

Thanks a lot for your answer!! It works!

Olli

Report message to a moderator

Re: Select Data from XMLTYPE (9.2.0.1) [message #129898 is a reply to message #126884]

Wed, 27 July 2005 03:58

ykevanranden
Messages: 4
Registered: July 2005
Location: Wageningen

Junior Member

I think you can only update parts of XML if you have create the XMLType column with a DTD attached to it. If you simply created the table as : create table test (y xmltype) it is internallystored as text (check ORACL XML manuals).

Report message to a moderator

Previous Topic:	how to call unix schema in java
Next Topic:	XML Publisher - Trbl with Repeat Fields / Code in Short Descr.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 17:19:34 CDT 2024