by Dênio Flávio Garcia da Silva

A Practical and Didactic Example with Namespaces

In an ideal world, this XML would be stored as XMLTYPE.

In the real world, it wasn’t.

The system was already in production, the integration was live, and the requirement was clear:
update a value inside an XML document stored in a CLOB column — without changing the data model.

This post shows how to do that using XMLQuery and XQuery, focusing on the essentials and avoiding unnecessary complexity.

Disclaimer

The XML structure used in this post is intentionally simplified for educational purposes.

It represents a real-world scenario where:

• XML is stored as CLOB
• the document uses namespaces
• the XML structure is controlled by an external system
• changing the column type is not an option

The same technique applies to more complex XML documents used in production environments.

The Problem

A table stores an XML document in a column called XML_DATA, defined as CLOB.

Inside this XML, there is a <Quantity> element for a cart item, and the requirement is straightforward:

Update <Quantity> from 2 to 3.

The goal here is not the business logic of a shopping cart, but how to safely update XML stored as CLOB in Oracle, using supported and maintainable features.

Example XML (Shopping Cart)

<Cart xmlns="http://example.com/cart">
  <Item>
    <Sku>ABC-001</Sku>
    <Name>Keyboard</Name>
    <Price>199.90</Price>
    <Quantity>2</Quantity>
  </Item>
</Cart>

<Cart xmlns="http://example.com/cart">
  <Item>
    <Sku>ABC-001</Sku>
    <Name>Keyboard</Name>
    <Price>199.90</Price>
    <Quantity>2</Quantity>
  </Item>
</Cart>

Key points:

• The document defines a default namespace
• The XML is stored as CLOB, not XMLTYPE
• The structure is minimal and easy to follow

Why Not UPDATEXML?

Some legacy Oracle code still relies on UPDATEXML.

While it may work in older environments, it is deprecated and not recommended for modern Oracle versions.
For maintainable and future-proof code, XMLQuery with XQuery is the correct approach.

XMLQuery and XQuery in a Nutshell

• XMLQuery is Oracle’s interface to XQuery
• XQuery is the W3C standard language for querying and modifying XML
• XML documents are immutable, so updates follow this pattern:

copy → modify → return

This pattern is required by the XQuery specification and ensures that the original document is not modified directly.

Always Validate with a SELECT First

Before running an UPDATE, validate the XPath expression with a SELECT.

select xmlserialize(
         content xmlquery(
           'declare default element namespace "http://example.com/cart";
            /Cart/Item/Quantity'
           passing xmltype(t.xml_data)
           returning content
         )
         as clob
       ) as quantity_node
from my_table t;

select xmlserialize(
         content xmlquery(
           'declare default element namespace "http://example.com/cart";
            /Cart/Item/Quantity'
           passing xmltype(t.xml_data)
           returning content
         )
         as clob
       ) as quantity_node
from my_table t;

If this query returns the expected node, the update will work as intended.

Updating <Quantity> Using XMLQuery

Now for the actual update.

update my_table t
set t.xml_data =
  xmlserialize(
    content xmlquery(
      'declare default element namespace "http://example.com/cart";
       copy $d := .
       modify (
         replace value of node
           $d/Cart/Item/Quantity
         with "3"
       )
       return $d'
      passing xmltype(t.xml_data)
      returning content
    )
    as clob
  );

update my_table t
set t.xml_data =
  xmlserialize(
    content xmlquery(
      'declare default element namespace "http://example.com/cart";
       copy $d := .
       modify (
         replace value of node
           $d/Cart/Item/Quantity
         with "3"
       )
       return $d'
      passing xmltype(t.xml_data)
      returning content
    )
    as clob
  );

What this does

• Converts the CLOB to XMLTYPE
• Locates the <Quantity> element using a namespace-aware XPath
• Replaces its value
• Serializes the updated XML back to CLOB

No deprecated functions. No shortcuts. Fully explicit and easy to understand.

Why Not Just Use XMLTYPE?

Yes — for new systems, storing XML as XMLTYPE is the recommended approach.

However, in many real-world Oracle environments:

• the schema is already defined
• the XML format is owned by external systems
• changing the column type would require data migration, regression testing, and downtime

This post focuses on working safely with what already exists, instead of ignoring real-world constraints.

Final Notes and Good Practices

• Always validate your XPath with a SELECT before running an UPDATE
• Be explicit with namespaces
• Avoid deprecated XML functions
• Use:
  • XMLQuery for updates
  • XMLTable for relational queries
  • XMLExists for filtering

Conclusion

Working with XML stored as CLOB is not ideal — but it is common.

By using XMLQuery, respecting namespaces, and following the copy / modify / return pattern, it is possible to write clean, readable, and maintainable Oracle code that works well within real-world constraints.