sqlxmloracle-databasexqueryupdatexml

Updating Oracle XMLType Column Content For a Given Value

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?

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')