Updating Oracle XMLType Column Content For a Given Value

oracle, sql, updatexml, xml, xquery

Solution

this will update all answer nodes for question 2 that has 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')

Problem

I have a table, SECTION_ANSWER, in an Oracle 11g database which has an XMLType column. The XML is pretty simple, it follows the 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 '105' answer to be '205.' I have done something similar in the past using UPDATEXML. For example if I was to update questionID 1 which only has 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, I'm having trouble this time updating questionID 2, since there are multiple answer nodes and I do not know which node the content that needs to be updated will be in. Can anyone shed any light on how to perform this kind of update?

Original source