Updating the contents of an Oracle XMLType column for a given value

Question

I have a SECTION_ANSWER table in an Oracle 11g database that has an XMLType column. XML is pretty simple, it follows a structure:

<section sectionID="1"> <question questionID="1" questionType="text"> <answer>Green</answer> </question> <question questionID="2" questionType="multiselect"> <answer>101</answer> <answer>102</answer> <answer>105</answer> <answer>107</answer> </question> </section> 

I need to update the answer "105" to be "205." In the past, I have done something similar using UPDATEXML. For example, if I were to update questionID 1, which has only one answer, I could do something like:

 UPDATE SECTION_ANSWER sa SET sa.section_answerxml = updatexml(sa.section_answerxml, '//section[@sectionID="1"]/question[@questionID="1"]/answer/text()', 'BLUE') 

However, this time I am having problems updating questionID 2, as there are several answer nodes, and I don’t know which node the content to be updated should be updated. Can anyone shed some light on how to perform this type of update?

Answer 1

this will update all answer nodes for question 2, which contains 105.

 UPDATE SECTION_ANSWER sa SET sa.section_answerxml = updatexml(sa.section_answerxml, '//section[@sectionID="1"]/question[@questionID="2"]/answer[text()="105"]/text()', '205') 

or you can update by position

 UPDATE SECTION_ANSWER sa SET sa.section_answerxml = updatexml(sa.section_answerxml, '//section[@sectionID="1"]/question[@questionID="2"]/answer[position()=3]/text()', '205')