SQL Server XQuery Methods

Estimated study time: 10 minutes. Query and modify XML data stored in a SQL Server column.

SQL Server's xml data type comes with five built-in XQuery methods that let you query, extract, and modify XML values directly inside T-SQL.

query()

Returns untyped XML matching an XQuery expression.

SELECT XmlColumn.query('/Employee/Name') FROM Employees;

value()

Extracts a single scalar value from the XML and casts it to a SQL type.

SELECT XmlColumn.value('(/Employee/Name)[1]', 'VARCHAR(100)') FROM Employees;

exist()

Returns 1, 0, or NULL depending on whether the XQuery expression finds a match — useful in a WHERE clause.

SELECT * FROM Employees
WHERE XmlColumn.exist('/Employee/Name[text()="Ravi"]') = 1;

modify()

Inserts, updates, or deletes nodes inside an XML value using XML DML.

UPDATE Employees
SET XmlColumn.modify('replace value of (/Employee/Name/text())[1] with "Amit"')
WHERE EmpID = 1;

nodes()

Shreds XML into a rowset, turning repeating nodes into relational rows.

SELECT T.c.value('.', 'VARCHAR(50)') AS SkillName
FROM Employees
CROSS APPLY XmlColumn.nodes('/Employee/Skills/Skill') AS T(c);