Mastering Advanced SQL Techniques with MySQL/

...

/

Modifying XML Values

Modifying XML Values

Learn how to modify XML values in MySQL.

We'll cover the following...

Besides ExtractValue() for searching XML values, MySQL also offers the UpdateXML() function to—as the name suggests—update an existing XML value. While we could use an UPDATE statement to replace an XML value as a whole, the UpdateXML() function has the advantage that we can specifically update only parts of an XML document. We can also combine UpdateXML() with ExtractValue() for certain use cases.

The UPDATE statement

As with any kind of value, we can use an UPDATE statement to replace an XML value. For example, in a previous instance, we looked at the following catalog of books:

Press + to interact
<?xml version="1.0" encoding="UTF-8"?>
<catalog>
    <book id="bk101">
        <author>Gambardella, Matthew</author>
        <title>XML Developer's Guide</title>
        <genre>Computer</genre>
        <price>44.95</price>
        <publish_date>2000-10-01</publish_date>
        <description>An in-depth look at creating applications
      with XML.</description>
    </book>
    <book id="bk102">
        <author>Ralls, Kim</author>
        <title>Midnight Rain</title>
        <genre>Fantasy</genre>
        <price>5.95</price>
        <publish_date>2000-12-16</publish_date>
        <description>A former architect battles corporate zombies,
      an evil sorceress, and her own childhood to become queen
      of the world.</description>
    </book>
    <book id="bk103">
        <author>Corets, Eva</author>
        <title>Maeve Ascendant</title>
        <genre>Fantasy</genre>
        <price>5.95</price>
        <publish_date>2000-11-17</publish_date>
        <description>After the collapse of a nanotechnology
      society in England, the young survivors lay the
      foundation for a new society.</description>
    </book>
</catalog>

Suppose that this catalog is stored in an SQL table, called Book:

Press + to interact
-- The catalog of books
CREATE TABLE IF NOT EXISTS Book
(
    id       INT AUTO_INCREMENT NOT NULL,
    document TEXT               NOT NULL
);

In our online book store, we now want to update the book with the ID bk101 to change the price it is being sold for. Book holds the corresponding XML ...

Access this course and 1400+ top-rated courses and projects.