Oracle XML functions let SQL work with XML as data: you can query XML with XQuery, turn selected nodes into relational rows, construct XML from columns, and convert XML between database and text representations. The main tools are XMLQuery, XMLTable, XMLExists, XMLType, and the SQL/XML generation and serialization functions.
What Oracle XML functions do
Oracle does not have one function called XML that handles every task. Instead, Oracle Database provides a family of SQL/XML functions and the XMLType data type. Together, they allow XML to be stored, inspected, filtered, transformed, generated, and returned to applications. These capabilities are part of Oracle XML DB, the database component that supports XML processing.
The functions are useful when an application receives XML documents, when XML is stored in an XMLType column, or when query results must be delivered in an XML format. They can also bridge XML and ordinary SQL data: for example, a query can read values from repeated XML elements and return them as rows and columns.
XMLQueryevaluates an XQuery expression and returns XML content.XMLTablemaps XQuery results into relational rows and columns.XMLExiststests whether an XQuery expression returns a result.XMLElement,XMLForest, andXMLAggbuild XML from SQL values.XMLSerializeconverts XML content into a SQL character or binary representation.
XMLType and the shape of XML data
XMLType is Oracle’s SQL data type for XML. A table can have an XMLType column alongside ordinary columns, or a query can create an XMLType value while it runs. The actual storage and indexing choices depend on the database release and application design; XMLType is the logical type, not a promise that every document is stored in the same physical format.
XML is hierarchical, while a relational table is made of rows and columns. A document such as <order><item>...</item></order> can contain nested elements and repeated items. Decide whether you need the complete document, a particular value, or a row for each repeated element before choosing a function. Returning the whole XML document is different from extracting one text node, and both differ from converting repeating nodes into rows.
XML paths are case-sensitive: Item and item are different names. XML namespaces also matter. A path that works for an element without a namespace may return no matches when the document uses a default namespace, unless the query declares and uses a matching prefix.
Query XML with XMLQuery
XMLQuery is the usual starting point for evaluating an XQuery expression in SQL. It takes an XQuery string, receives values through a PASSING clause, and returns XML content. For example, assuming a table named purchase_orders has an XMLType column named payload, this query selects each order’s reference element:
SELECT XMLQUERY('/PurchaseOrder/Reference' PASSING payload RETURNING CONTENT) FROM purchase_orders;
The result is XML content, not automatically a plain SQL string. If the desired output is a scalar, use XMLCast to convert an XML result to a suitable SQL type. For instance, assuming the same table and an XML reference short enough for the chosen limit, a query can cast the selected text to VARCHAR2(100):
SELECT XMLCAST(XMLQUERY('/PurchaseOrder/Reference/text()' PASSING payload RETURNING CONTENT) AS VARCHAR2(100)) FROM purchase_orders;
The target SQL type should fit the expected value. Casting an XML node that contains complex content, or a value that cannot be represented by the target type, can cause conversion errors or lose structure. Use XMLQuery when you need an XQuery expression or want an XML result; use XMLCast when a SQL scalar is the intended result.
Turn XML nodes into rows with XMLTable
XMLTable is often the most practical choice when XML has repeating elements that should be queried like table rows. It evaluates an XQuery row expression and defines columns for values taken from each matching node. Suppose payload contains purchase orders whose line items have Description and Quantity children. The following query returns one SQL row per line item:
SELECT d.id, x.item_name, x.qty FROM purchase_orders d, XMLTABLE('/PurchaseOrder/LineItems/LineItem' PASSING d.payload COLUMNS item_name VARCHAR2(100) PATH 'Description', qty NUMBER PATH 'Quantity') x;
Here the row path selects each LineItem; the column paths are evaluated relative to each selected item. The SQL types in the COLUMNS clause determine how the values are exposed to the rest of the query. This makes it possible to filter, join, group, and sort the extracted values using ordinary SQL.
For namespaced XML, declare the namespace in the XMLTable expression and use its prefix in the row and column paths. For example, a document using the namespace urn:example:orders can be queried with XMLNAMESPACES('urn:example:orders' AS "p"), followed by paths such as /p:PurchaseOrder/p:LineItems/p:LineItem. A common cause of empty results is writing a path without accounting for the document’s namespace.
Filter with XMLExists
XMLExists answers a yes-or-no question: does the XQuery expression produce a result for this XML value? It is commonly used in a SQL WHERE clause to keep or reject rows without returning the matched XML. For example, assuming the same table and that a line item has a Quantity element, the following filters for documents containing an item with a quantity greater than five:
SELECT id FROM purchase_orders WHERE XMLEXISTS('/PurchaseOrder/LineItems/LineItem[Quantity > 5]' PASSING payload);
The XQuery inside XMLExists determines whether a result exists; it does not return the matching quantity as a SQL column. Use XMLTable when the matched values need to appear in the result or be used as relational columns. Use XMLExists when the key requirement is simply to test for a matching node or condition.
Create XML from SQL values
Oracle provides functions for constructing XML from relational data. XMLElement creates an element, XMLAttributes supplies attributes, and XMLForest creates elements from one or more SQL expressions. For example, assuming an orders table has order_id, customer_name, and status columns, this produces an XML element for each row:
SELECT XMLELEMENT("order", XMLATTRIBUTES(order_id AS "id"), XMLFOREST(customer_name AS "customer", status AS "status")) FROM orders;
XMLAgg aggregates XML values, making it useful for combining elements from multiple rows. It can be placed inside an outer XMLElement to produce a document-like result. Include an ORDER BY inside the XMLAgg expression when the order of aggregated elements matters; relational row order is not guaranteed unless it is specified.
These construction functions handle XML structure more safely than assembling markup through string concatenation. String concatenation can produce malformed XML when values contain characters such as ampersands or angle brackets, and it makes escaping and nesting harder to manage. Use XML generation functions when the output is genuinely XML rather than a text string that merely resembles markup.
Parse, serialize, and convert XML
XMLParse parses character input as XML and returns an XML value. Its DOCUMENT form is intended for a document with one document element; CONTENT is for XML content that may consist of multiple top-level items. Parsing validates the input as XML, so malformed markup can raise an error. Do not mark input as already well formed unless that assertion is true.
XMLSerialize converts XML content to a SQL character or binary type, such as CLOB, for applications or interfaces that need serialized markup. It can also control indentation. Serialization is not the same as selecting a text node: it returns markup representation, while an XMLQuery expression selecting text() targets text within the XML. Choose based on whether the consumer needs the XML tags or only a value.
Oracle also supports XMLType constructors and conversions, but conversion behavior depends on the source and target types. When moving between XMLType, character data, and scalar SQL values, be explicit about whether the intended result is a full document, a fragment, or a single value.
Choosing functions and avoiding legacy patterns
For new SQL, prefer the SQL/XML and XQuery functions that match the task. XMLQuery handles XML-oriented selection or transformation; XMLTable exposes selected content as rows and columns; XMLExists checks for matches; generation functions build markup; and XMLSerialize produces serialized output.
Older examples often use EXTRACT and EXTRACTVALUE to navigate XML. Oracle deprecated these functions in Oracle Database 11g Release 2, so new code should generally use XMLQuery and XMLTable instead. Existing systems may still contain older expressions, and replacing them safely requires checking their precise behavior, namespaces, return types, and null handling. Do not assume a mechanical text replacement will preserve every result.
Keep the expected output in mind when selecting a function. If a report needs one row per XML item, XMLTable is usually clearer than extracting an entire fragment and parsing it later. If an application needs the original document, keep or serialize the XML rather than flattening it prematurely. If only one scalar is needed, use a focused query and an explicit conversion.
Performance and troubleshooting
XML query performance depends on document size, query shape, storage, indexes, and database version. Avoid extracting a large XML fragment when only a small value is required. For repeated queries over large XML datasets, review Oracle’s XML DB indexing options and execution plans rather than assuming that an XML path is automatically indexed. Index design should reflect the paths and predicates actually used by the workload.
- Check element spelling and capitalization against the source document.
- Check namespace declarations and prefixes when a path unexpectedly returns no rows.
- Confirm whether the expression returns a node, text, or multiple matches before casting it.
- Match XMLTable column types to real data, and account for missing or nonnumeric values.
- Test documents with empty elements, optional nodes, and repeated elements, not just one typical sample.
One recurring misconception is that XMLExists returns the matching node. It does not; it tests whether a result exists. Another is that XMLQuery automatically returns a VARCHAR2 value. It returns XML content, so use XMLCast or another appropriate conversion when the consumer needs a SQL scalar.
Frequently Asked Questions
What is the main XML function in Oracle?
There is no single function for every XML task. XMLQuery is the central choice for XQuery evaluation, while XMLTable, XMLExists, XMLType, and the generation functions serve distinct purposes.
How do I extract a value from XML in Oracle SQL?
Use XMLQuery to select the desired node or text, then XMLCast to convert the result to a SQL type when necessary. For several values or repeating elements, XMLTable is usually a better fit.
Why does my Oracle XML query return no rows?
Check the exact element names, capitalization, path context, and namespaces. A default namespace in the document is a frequent reason an apparently correct unprefixed path finds nothing.
Are EXTRACT and EXTRACTVALUE still recommended?
Oracle deprecated them in 11g Release 2. They may appear in existing applications, but XMLQuery and XMLTable are the preferred options for new SQL and are generally the better basis for modernization.
Should I use XMLQuery or XMLTable?
Use XMLQuery when you want an XML result from an XQuery expression. Use XMLTable when you want matching XML content projected into SQL rows and typed columns that can be joined or filtered.
Related
- How to Sum Just Subtotals in Excel Without Double-Counting
- How to Use SUBTOTAL in Excel
- X-Arcade Programming Software: What You Need to Know
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF Year in Date: Formula & ExamplesTo count cells containing a date in a specific year, use =SUMPRODUCT(--(YEAR(range)=year)) or, in Excel 2021 and Microsoft…
- Excel Formula for Percentage of Total: Clear ExamplesTo calculate a percentage of a total in Excel, divide the part by the total: if the value…
- Excel Formula to Find and Replace Text in Cells: ExamplesTo replace text inside a cell with an Excel formula, use SUBSTITUTE. If the original text is in…
- Excel Delete Every X Rows: Safe Methods and FormulasYes. In Microsoft Excel for Microsoft 365 on Windows, add a helper column to mark every xth data…
- Microsoft Excel COUNTIF Formula: Syntax and ExamplesIf product names are in A2:A100, count cells containing Apples with =COUNTIF(A2:A100,"Apples"). COUNTIF counts cells in a range…
- How to Change the Year in an Excel Calendar FormulaTo change the year in an Excel calendar formula, update the year cell the calendar refers to; the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.