VALUE (Transact‑SQL)
Category: Scalar Function → String Functions
Syntax
VALUE ( xml_data_type [ , 'xquery_expression' ] )Parameters
xml_data_type | Expression of type xml that contains the XQuery expression. |
'xquery_expression' | Optional XQuery expression that returns a scalar value. If omitted, the function returns the entire XML instance. |
Return Type
Returns nvarchar(max) containing the scalar value extracted from the XML instance.
Remarks
- The
VALUEmethod extracts a value from an XML instance based on an XQuery expression. - Only a single scalar value can be returned; if the XQuery expression returns multiple nodes, an error is raised.
- Use the
.value()method on an XML column or variable to retrieve the value in a query.
Examples
Extract the price of a product from an XML column:
DECLARE @Products xml =
N'<catalog>
<product>
<name>Bike</name>
<price>129.99</price>
</product>
<product>
<name>Helmet</name>
<price>39.95</price>
</product>
</catalog>';
SELECT
Product.value('(name)[1]','nvarchar(50)') AS ProductName,
Product.value('(price)[1]','decimal(10,2)') AS ProductPrice
FROM @Products.nodes('/catalog/product') AS T(Product);