Menu
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?
355
XMLType is a system-defined opaque type for handling XML data. It as predefined member functions on it to extract XML nodes and fragments. You can create columns of XMLType and insert XML documents into it.
To access the SYS. XMLTYPE 'type', you will need to access the 'Complex' datatype list and navigate to the 'SYS' schema. You can then set any of the XML specific options. You can read the nitty-gritty details on XMLTYPE here.
You should use the query() Method if you want to get a part of your XML. If you want the value from a specific node you should use value() Method. Update: If you want to shred your XML to multiple rows you use nodes() Method.
Using the value() and exist() methods to retrieve values from an xml type column. The following example shows using both the value() method and the exist() method of the xml data type. The value() method is used to retrieve ProductModelID attribute values from the XML.
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')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
