T-SQL Tutorial

SQL Server XML


The XML data type in SQL Server allows you to store and manipulate XML data in a SQL Server database. The XML data type can be used to store both well-formed and valid XML documents, as well as fragments of XML data. The data can be queried and manipulated using the built-in functions and methods provided by SQL Server.

SQL Server provides several XML functions and data type methods that allow developers to work with XML data within the database. These functions and methods provide various options for querying, modifying, and transforming XML data into other data types.


XML Data Type Methods

SQL Server provides the following XML data type methods:
value(): Returns a scalar value from the specified XPath expression.
query(): Returns a result set of nodes based on the specified XPath expression.
modify(): Performs data modification operations on the XML data.
nodes(): Shreds the XML data into multiple rows and columns, returning the result set.
exist(): Returns a Boolean value indicating whether the specified XPath expression returns any nodes.


XML Functions

SQL Server provides the following XML functions:
XQuery(): Executes an XQuery expression against the XML data and returns the result as an XML data type.
XML Data Modification Functions: These functions allow modification of XML data. The commonly used functions are: modify(), nodes(), query().


XML examples

SQL Server provides several options for working with XML data within a database using Transact-SQL. The following is a step-by-step guide to using XML in SQL Server:


1. Create a table with an XML column: You can create a table with an XML column to store XML data. The following example creates a table named "Customers" with an XML column named "Orders":

CREATE TABLE Customers
(
CustomerID int PRIMARY KEY,
CustomerName varchar(50),
Orders xml
);


2. Insert XML data into the table: You can insert XML data into the XML column of a table using the INSERT statement. The following example inserts an XML document into the "Orders" column of the "Customers" table:

INSERT INTO Customers VALUES (1, 'John Doe',
'<Orders>
<Order>
<OrderID>100</OrderID>
<OrderDate>2022-01-01</OrderDate>
<ProductName>Product 1</ProductName>
<Quantity>2</Quantity>
</Order>
<Order>
<OrderID>101</OrderID>
<OrderDate>2022-02-01</OrderDate>
<ProductName>Product 2</ProductName>
<Quantity>1</Quantity>
</Order>
</Orders>')


3. Query XML data using XQuery: You can query XML data using XQuery expressions. The following example retrieves the "ProductName" and "Quantity" of all orders for the customer with "CustomerID" of 1:

SELECT CustomerName, Orders.query('
for $order in /Orders/Order
return
<Order>
<ProductName>{data($order/ProductName)}</ProductName>
<Quantity>{data($order/Quantity)}</Quantity>
</Order>
') as OrderDetails
FROM Customers
WHERE CustomerID = 1;


4. Modify XML data using XQuery: You can modify XML data using the "modify" method. The following example adds a new order to the customer with "CustomerID" of 1:

UPDATE Customers
SET Orders.modify('
insert
<Order>
<OrderID>102</OrderID>
<OrderDate>2022-03-01</OrderDate>
<ProductName>Product 3</ProductName>
<Quantity>3</Quantity>
</Order>
into /Orders[1]
')
WHERE CustomerID = 1


In conclusion, the above steps provide a basic introduction to using XML in SQL Server with Transact-SQL. These functions allow developers to manipulate XML data, extract information from XML data, and transform XML data into other data types as needed.