XMLType/XML DB Awareness, Legacy XML Workloads, and Migration Boundaries

Learning outcomes

ServiceHub acquires a maintenance system whose external partners exchange signed, namespace-qualified XML documents validated against long-lived contracts. “Convert it all to JSON” sounds modern but can silently break namespaces, element order, XML Schema validation, digital-signature/canonicalization behavior and partner APIs. Oracle XML DB and SQL data type XMLType remain a supported maintenance surface; the engineering question is which XML semantics are contractual and which data should be projected into relational/JSON forms for easier new development.

1. XMLType keeps XML as XML

XMLType is Oracle's SQL type for XML documents/fragments. Oracle XML DB supplies storage/query/schema/repository capabilities around it. Starting in 26ai, when COMPATIBLE >= 23.0.0.0, newly created XMLType storage defaults to Transportable Binary XML (TBX), designed for cross-platform transport and efficient XML processing.

SELECT name,valueFROM v$parameterWHERE name='compatible';

2. Build a namespaced legacy work-order envelope

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh24_xml_messages PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh24_xml_messages (  message_id NUMBER PRIMARY KEY,  received_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,  payload XMLTYPE NOT NULL);INSERT INTO sh24_xml_messages(message_id,payload)VALUES(  1,  XMLTYPE(q'~<wo:WorkOrder xmlns:wo="urn:servicehub:workorder:v1">    <wo:Id>7001</wo:Id>    <wo:Status>OPEN</wo:Status>    <wo:Priority>2</wo:Priority>    <wo:Customer>      <wo:Name>Ada Services</wo:Name>    </wo:Customer>  </wo:WorkOrder>~'));COMMIT;

3. XMLExists filters; XMLQuery returns XML content

SELECT message_idFROM sh24_xml_messages mWHERE XMLEXISTS(  'declare namespace wo="urn:servicehub:workorder:v1";   /wo:WorkOrder[wo:Status="OPEN"]'  PASSING m.payload);-- Expected: message_id 1.

SELECT XMLSERIALIZE(         CONTENT XMLQUERY(           'declare namespace wo="urn:servicehub:workorder:v1";            /wo:WorkOrder/wo:Customer'           PASSING payload           RETURNING CONTENT         )         AS CLOB       ) AS customer_xmlFROM sh24_xml_messagesWHERE message_id=1;

4. XMLTable turns selected XML structure into relational columns

SELECT  m.message_id,  x.work_order_id,  x.status_code,  x.priority_code,  x.customer_nameFROM sh24_xml_messages m,     XMLTABLE(       XMLNAMESPACES(         'urn:servicehub:workorder:v1' AS "wo"       ),       '/wo:WorkOrder'       PASSING m.payload       COLUMNS         work_order_id NUMBER        PATH 'wo:Id',         status_code   VARCHAR2(12)  PATH 'wo:Status',         priority_code NUMBER        PATH 'wo:Priority',         customer_name VARCHAR2(100) PATH 'wo:Customer/wo:Name'     ) x;

This projection does not mutate or discard the XML. It lets ordinary SQL consume selected values while the original partner payload remains available for audit/replay/round-trip requirements.

5. Current SQL/XML replaces deprecated extract/extractValue patterns

Legacy code may contain Oracle SQL functions extract and extractValue, deprecated since 11g Release 2. Maintain them carefully while planning migration to standards-based XMLQuery, XMLTable, XMLExists and XMLCast. Do not introduce new application code on the deprecated functions.

6. Deliberately malformed XML fails at parse time

INSERT INTO sh24_xml_messages(message_id,payload)VALUES(  2,  XMLTYPE('<WorkOrder><Id>7002</Id></WorkOrderBROKEN>'));-- Expected XML parser failure, typically including ORA-31011-- plus LPX/XML parsing diagnostics.

The safe repair is not to store unvalidated arbitrary text in XMLType. Fix or quarantine the producer payload, preserve the raw envelope separately only if incident/audit requirements demand it, and accept into XMLType once it is well-formed.

7. Wrong migration: regex/string-replace XML into JSON

Text replacement such as changing <wo:Status> into "status": ignores namespace URIs, entity escaping, attributes, mixed content, element ordering, repeated elements and schema typing. It can also invalidate XML signatures. The migration can produce syntactically valid JSON whose business meaning is wrong.

8. Safer staged migration boundary

CREATE TABLE sh24_xml_projection (  message_id NUMBER PRIMARY KEY,  work_order_id NUMBER NOT NULL,  status_code VARCHAR2(12) NOT NULL,  priority_code NUMBER,  customer_name VARCHAR2(100),  source_xml XMLTYPE NOT NULL);INSERT INTO sh24_xml_projectionSELECT  m.message_id,  x.work_order_id,  x.status_code,  x.priority_code,  x.customer_name,  m.payloadFROM sh24_xml_messages m,     XMLTABLE(       XMLNAMESPACES(         'urn:servicehub:workorder:v1' AS "wo"       ),       '/wo:WorkOrder'       PASSING m.payload       COLUMNS         work_order_id NUMBER        PATH 'wo:Id',         status_code   VARCHAR2(12)  PATH 'wo:Status',         priority_code NUMBER        PATH 'wo:Priority',         customer_name VARCHAR2(100) PATH 'wo:Customer/wo:Name'     ) x;COMMIT;

SELECT JSON_OBJECT(         'workOrderId' VALUE work_order_id,         'status'      VALUE status_code,         'priority'    VALUE priority_code,         'customer'    VALUE customer_name         RETURNING JSON       ) AS json_api_documentFROM sh24_xml_projection;

The original XML remains available while the new application can consume relational/JSON projections. Once external contracts and round-trip requirements are retired, the XML retention policy can be reconsidered deliberately.

9. XML migration acceptance tests

10. Driver/tool boundary

SQL/SQLcl/SQL Developer can operate XMLType directly. Some XMLType-specific APIs are not fully supported by thin JDBC paths; Oracle XML DB documents OCI/thick-driver differences for certain XMLType functions. New application code should prefer portable SQL/XML result shapes unless an XML-specific client API is actually required.

11. Cleanup

DROP TABLE sh24_xml_projection PURGE;DROP TABLE sh24_xml_messages PURGE;

12. Production judgment

Keep XML when XML-specific semantics or external contracts are real requirements. Use XMLTable/XMLExists/XMLQuery/XMLCast for maintainable current SQL/XML. Migrate only the portions whose document semantics can be mapped and verified; preserving an authoritative source XML during transition is often cheaper than a risky flag-day conversion.

In 26ai with COMPATIBLE >= 23.0, XMLType defaults to Transportable Binary XML. The mandatory lab needs no pack, extra-cost option, restart or configuration change. XML DB remains available, but new designs should earn XML complexity from a real contract rather than historical inertia. Lesson 3 shifts from hierarchical documents to another specialized type whose semantics cannot safely be reduced to generic numeric columns: geospatial data.

Authoritative references

• Oracle XML DB Developer's Guide — XMLType/XML DB maintenance
• XQuery and Oracle XML DB — XMLQuery/XMLTable/XMLExists/XMLCast
• How to Use Oracle XML DB — 26ai Transportable Binary XML default
• XML DB Restrictions — driver/type restrictions
• SQL/XML Reference — current SQL/XML syntax