CODEWITHSUNDEEP
xmloracle-databasexpathsql-update
Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Update XML in Oracle using UPDATEXML

I have an XML document like this one and I have to update value of The time of begin by new text. How to do this with UPDATEXML function, i.e. what is the XPath expression?

<dok>
    <dataSet>
        <foo>bla bla</foo>
        <field>START_DATE</field>
        <label>The time of begin</label>
    </dataSet>
    <dataSet>
        <bar>bla bla</bar>
        <label>The time of end</label>
        <field>END_DATE</field>
    </dataSet>
</dok>

My first idea was like this one:

UPDATE MY_TABLE SET 
XML_COLUMN = UPDATEXML(XML_COLUMN, '/dok/dataSet/field/text()="START_DATE"/../label/text()', 'The date of start');

I don't like to use axis like following-sibling, because inside dataSet you may have various other elements in unknown order.

The rule shall be like: Update "label" at "dataSet" where "field" = 'START_DATE'

Upvotes: 1

Views: 658

Answers (1)

Mathias M&#252;ller
Mathias M&#252;ller

Reputation: 22617

To qualify elements like field (for example, only selecting one with a certain text content) use angle brackets. Also, it seems to me that the function is called UPDATEXML, not XMLUPDATE (see this page).

Try the following:

UPDATE MY_TABLE SET XML_COLUMN =
   UPDATEXML(XML_COLUMN,
   '/dok/dataSet[field = "START_DATE"]/label/text()','The date of start');

Note: I'm familiar with XPath, not with SQL. But as far as I can see there might still be a missing WHERE clause in the above.

Upvotes: 2

Related Questions