Modern relational databases bridge the gap between structured relational storage and hierarchical XML data. SQL Server provides robust features for both exporting result sets as XML and manipulating XML documents stored directly within database columns using XQuery.

Generaitng XML from Relational Queries

SQL Server allows you to transform query output into XML structures using the FOR XML clause. The PATH mode offers the most flexibility for defining custom XML hierarchies.

-- Sample table setup
CREATE TABLE Users (
   UserID INT IDENTITY PRIMARY KEY,
   GivenName NVARCHAR(50),
   FamilyName NVARCHAR(50),
   YearsOld INT,
   Gender NVARCHAR(10)
);

-- Exporting data with custom path mapping
SELECT 
   UserID AS [@id],
   GivenName AS [Identity/First],
   FamilyName AS [Identity/Last],
   YearsOld AS [Metadata/Age]
FROM Users
FOR XML PATH('UserEntry'), ROOT('UserDirectory');

Manipulating XML Data Types

The XML data type enables native storage and efficient querying. The .modify() method utilizes XQuery to perform Data Manipulation Langugae (DML) operations on XML blobs.

DECLARE @catalog XML = '<item><title>SQL Guide</title><price>29</price></item>';

-- Removing an element
SET @catalog.modify('delete (/item/price)');

-- Inserting a new node
SET @catalog.modify('insert <currency>USD</currency> as last into (/item)[1]');

-- Updating a node value using a SQL variable
DECLARE @newPrice INT = 35;
SET @catalog.modify('replace value of (/item/title/text())[1] with sql:variable("@newPrice")');

Querying Namespaced XML

When working with XML containing namespaces, you must explicitly declare those namespaces within your XQuery expressions using the query() method to correctly identify nodes.

DECLARE @xmlData XML = '<record xmlns="https://api.example.com/schema">
                           <identifier>A-100</identifier>
                         </record>';

SELECT @xmlData.query('
   declare namespace ex = "https://api.example.com/schema";
   <summary>
       { data(ex:record/ex:identifier) }
   </summary>
') AS Result;

By combining standard SQL query capabilities with XQuery-based XML manipulation, developers can handle complex semi-structured data requirements directly within the database layer.