The XML data type in SQL Server provides a set of methods that allow you to query and manipulate XML data efficiently. The commonly used methods for this purpose are value(), query(), and nodes(). Here's an overview of each method and how to use them:
1. The value() Method
The value() method is used to extract a scalar value from an XML document. It takes two arguments: an XQuery expression and the SQL data type to which the result will be converted.
Syntax:
xml.value('XQueryExpression', 'SQLDataType')
Example:
Suppose you have an XML column named ProductInfo that contains information about products.
<Product>
<ID>1</ID>
<Name>Widget</Name>
<Price>19.99</Price>
</Product>
To extract the product name, you could use:
SELECT ProductInfo.value('(/Product/Name)[1]', 'nvarchar(100)') AS ProductName
FROM Products
2. The query() Method
The query() method is used when you want to return XML fragments instead of scalar values. It returns the result as XML based on an XQuery expression.
Syntax:
xml.query('XQueryExpression')
Example:
To get the complete XML of a product record, you can use:
SELECT ProductInfo.query('(/Product)[1]') AS ProductDetails
FROM Products
3. The nodes() Method
The nodes() method is used to shred the XML into multiple rows. It allows you to work with each node as if it were a row in a table. This method generally requires an XPath expression and returns a rowset that can be queried further.
Syntax:
xml.nodes('XQueryExpression') AS AliasName(Column1)
Example:
If you have an XML column that contains multiple products, and you want to retrieve each product as a separate row, you could do something like this:
<Products>
<Product>
<ID>1</ID>
<Name>Widget</Name>
<Price>19.99</Price>
</Product>
<Product>
<ID>2</ID>
<Name>Gadget</Name>
<Price>29.99</Price>
</Product>
</Products>
You can use the nodes() method to get a list of products:
SELECT
T.C.value('(ID)[1]', 'int') AS ProductID,
T.C.value('(Name)[1]', 'nvarchar(100)') AS ProductName,
T.C.value('(Price)[1]', 'decimal(18,2)') AS ProductPrice
FROM ProductsTable
CROSS APPLY ProductsColumn.nodes('/Products/Product') AS T(C)
Summary
- value(): Use this method to extract single scalar values.
- query(): Use this method when you want to return XML fragments.
- nodes(): Use this method to convert XML nodes into rows, facilitating easier querying for multiple entries.
These methods allow you to interact with XML data in a structured manner, enabling you to extract, query, and manipulate XML content within a relational database context.