value() method (xml data type)

  • Artikel

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Performs an XQuery against XML and returns a value of SQL type. This method returns a scalar value.

You typically use this method to extract a value from an XML instance stored in an xml type column, parameter, or variable. In this way, you can specify SELECT queries that combine or compare XML data with data in non-XML columns.

Syntax

value ( XQuery , SQLType )

Arguments

XQuery

The XQuery expression, a string literal, that retrieves data inside the XML instance. The XQuery must return at most one value. Otherwise, an error is returned.

SQLType

The preferred SQL type, a string literal, to be returned. The return type of this method matches the SQLType parameter. SQLType can be a user-defined SQL data type.

Note

SQLType can't be one of the following data types: xml, image, text, ntext, sql_variant, or a common language runtime (CLR) user-defined type.

The value() method uses the Transact-SQL CONVERT operator implicitly. value() tries to convert the result of the XQuery expression, the serialized string representation, from XML Schema Definition (XSD) type to the corresponding SQL type specified by Transact-SQL conversion. For more information about type casting rules for CONVERT, see CAST and CONVERT.

For performance reasons, you can use exist() with sql:column() instead of using the value() method in a predicate, to compare with a relational value. This exist() example is shown later in this article.

Examples

The Transact-SQL code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

A. Use the value() method against an XML type variable

In the following example, an XML instance is stored in a variable of xml type. The value() method retrieves the ProductID attribute value from the XML. The value is then assigned to an int variable.

DECLARE @myDoc XML;
DECLARE @ProdID INT;

SET @myDoc = '<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
  <Warranty>1 year parts and labor</Warranty>
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>';

SET @ProdID = @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int');
SELECT @ProdID;

A value of 1 is returned as a result.

Although there's only one ProductID attribute in the XML instance, the static typing rules require you to explicitly specify that the path expression returns a singleton. Therefore, the [1] is added to the end of the path expression. For more information about static typing, see XQuery and Static Typing.

B. Use the value() method to retrieve an integer value from an XML type column

The following query is specified against an xml type column (CatalogDescription) in the AdventureWorks2022 database. The query retrieves ProductModelID attribute values from each XML instance stored in the column.

SELECT CatalogDescription.value(
    'declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
       (/PD:ProductDescription/@ProductModelID)[1]', 'int') AS Result
FROM Production.ProductModel
WHERE CatalogDescription IS NOT NULL
ORDER BY Result DESC;

Note from the previous query:

  • The namespace keyword is used to define a namespace prefix.

  • Per static typing requirements, [1] is added at the end of the path expression in the value() method to explicitly indicate that the path expression returns a singleton.

Here's the result set.

35
34
28
25
23
19

C. Use the value() method to retrieve a string value from an XML type column

The following query is specified against the xml type column (CatalogDescription) in the AdventureWorks2022 database. The query retrieves ProductModelName attribute values from each XML instance stored in the column.

SELECT CatalogDescription.value(
    'declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
       (/PD:ProductDescription/@ProductModelName)[1]', 'varchar(50)') AS Result
FROM Production.ProductModel
WHERE CatalogDescription IS NOT NULL
ORDER BY Result DESC;

Note from the previous query:

  • The namespace keyword is used to define a namespace prefix.

  • Per static typing requirements, [1] is added at the end of the path expression in the value() method to explicitly indicate that the path expression returns a singleton.

Here's the result set.

Touring-2000
Touring-1000
Road-450
Road-150
Mountain-500
Mountain 100

D. Use the value() and exist() methods to retrieve values from an XML type column

The following example shows using both the value() method and the exist() method of the xml data type. The value() method is used to retrieve ProductModelID attribute values from the XML. The exist() method in the WHERE clause is used to filter the rows from the table.

The query retrieves product model IDs from XML instances that include warranty information (the <Warranty> element) as one of the features. The condition in the WHERE clause uses the exist() method to retrieve only the rows satisfying this condition.

SELECT CatalogDescription.value(
    'declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
           (/PD:ProductDescription/@ProductModelID)[1]', 'int') AS Result
FROM Production.ProductModel
WHERE CatalogDescription.exist(
    'declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
     declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";

     /PD:ProductDescription/PD:Features/wm:Warranty') = 1;

Note from the previous query:

  • The CatalogDescription column is a typed XML column. This means that it has a schema collection associated with it. In the Modules and Prologs - XQuery Prolog, the namespace declaration is used to define the prefix that is used later in the query body.

  • If the exist() method returns 1 (true), it indicates that the XML instance includes the <Warranty> child element as one of the features.

  • The value() method in the SELECT clause then retrieves the ProductModelID attribute values as integers.

Here's the partial result:

19
23
...

E. Use the exist() method instead of the value() method

For performance reasons, instead of using the value() method in a predicate to compare with a relational value, use exist() with sql:column(). For example:

CREATE TABLE T (c1 INT, c2 VARCHAR(10), c3 XML);
GO

SELECT c1, c2, c3
FROM T
WHERE c3.value('(/root/@a)[1]', 'integer') = c1;
GO

This code can be rewritten as follows:

SELECT c1, c2, c3
FROM T
WHERE c3.exist('/root[@a=sql:column("c1")]') = 1;
GO

Related content