SQL Server XML Data Type

SQL Server’s xml data type stores XML instances in a column or variable. It is useful for semi-structured or hierarchical data that needs structural queries or partial updates. If the document is only archived and must be preserved as exact text, consider nvarchar(max) instead: SQL Server’s native XML representation preserves XML content, but not details such as insignificant whitespace, attribute order, namespace prefixes, or the XML declaration. Microsoft’s XML data type guide describes these storage tradeoffs.

XML can be untyped, or associated with an XML schema collection that validates the instance and provides type information. See Microsoft’s guide to typed XML versus untyped XML.

Query XML values

The XML methods let a query return XML, extract a SQL scalar, test for a matching node, update XML, or turn matching nodes into rows. This example uses the value() method to extract an attribute and an element from an untyped XML variable:

DECLARE @doc xml = N'
<orders>
  <order id="101"><status>pending</status></order>
</orders>';

SELECT @doc.value('(/orders/order/@id)[1]', 'int') AS order_id,
       @doc.value('(/orders/order/status/text())[1]', 'nvarchar(20)') AS order_status;

Result:

The [1] in each XQuery expression declares a singleton result, which value() requires. SQL Server also provides these methods:

• query() returns XML selected by an XQuery expression.
• exist() returns 1 when the expression returns a nonempty result, 0 when it returns an empty result, and NULL for a NULL XML instance.
• modify() applies XML DML and can be used only in the SET clause of an UPDATE statement.
• nodes() maps selected XML nodes to a rowset for relational queries.

Return one row per repeated XML element

Use nodes() when an XML instance contains repeated elements and the query needs one relational row for each match. The rowset gives each value() call a context node for that element:

DECLARE @doc xml = N'
<orders>
  <order id="101"><status>pending</status></order>
  <order id="102"><status>shipped</status></order>
</orders>';

SELECT OrderNode.value('(@id)[1]', 'int') AS order_id,
       OrderNode.value('(status/text())[1]', 'nvarchar(20)') AS order_status
FROM @doc.nodes('/orders/order') AS OrderRows(OrderNode);

Result:

See Microsoft’s nodes() method reference for how the rowset establishes an XML context for each matching node.

See Microsoft’s XML data type methods for the method syntax and XQuery rules.

Typed and untyped XML

An untyped xml column accepts XML without validating it against an XML schema. A typed XML column is associated with an XML schema collection; SQL Server validates values against that schema and can use the schema’s type information while processing XQuery. See Microsoft’s guide to typed XML versus untyped XML.

XML indexes

Consider XML indexes when a workload frequently queries large XML values for small portions of the documents. XML index maintenance adds work to inserts, updates, and deletes, so check the execution plan and workload before adding one. An XML column can have one primary XML index; a clustered index on the table’s primary key must exist first. Secondary PATH, VALUE, and PROPERTY XML indexes require the primary XML index. See Microsoft’s XML index guidance.

An XML value’s stored representation is limited to 2 GB. XML values cannot be compared, sorted, or used as relational index key columns. These limits, and the cost of XML index maintenance, are reasons to promote frequently filtered or joined values into ordinary relational columns when the data model permits it. See Microsoft’s XML data type limitations.