SQL Server XML Data Type

Estimated study time: 16 minutes. Store and query semi-structured data natively.

SQL Server offers a native XML data type that lets you store an entire XML document or fragment inside a single column, then query and modify it using built-in methods instead of treating it as plain text.

Creating a Column with XML Data Type

CREATE TABLE ProductCatalog (
  ProductID INT PRIMARY KEY,
  ProductDetails XML
);

Inserting XML Data

INSERT INTO ProductCatalog (ProductID, ProductDetails)
VALUES (1, '<Product><Name>Laptop</Name><Price>55000</Price></Product>');

Typed vs Untyped XML

An XML column can be untyped (any well-formed XML is accepted) or typed, where it's bound to an XML schema collection so SQL Server can validate the structure on insert.

CREATE XML SCHEMA COLLECTION ProductSchema AS N'...';
CREATE TABLE ProductCatalog (
  ProductDetails XML(ProductSchema)
);

Querying XML with .value()

The .value() method extracts a scalar value from an XML node.

SELECT ProductDetails.value('(/Product/Name)[1]', 'VARCHAR(100)') AS ProductName
FROM ProductCatalog;

Querying XML with .query()

The .query() method returns an XML fragment matching an XQuery expression.

SELECT ProductDetails.query('/Product/Name') AS NameNode
FROM ProductCatalog;

Modifying XML with .modify()

The .modify() method lets you insert, update, or delete nodes inside stored XML using XML DML.

UPDATE ProductCatalog
SET ProductDetails.modify('replace value of (/Product/Price/text())[1] with "59000"')
WHERE ProductID = 1;

Shredding XML with .nodes()

The .nodes() method turns XML nodes into a rowset, which is useful for joining XML data with relational tables.

SELECT T.c.value('.', 'VARCHAR(50)') AS Tag
FROM ProductCatalog
CROSS APPLY ProductDetails.nodes('/Product/*') AS T(c);