XML Data Type: Method VALUE

Description and code samples
Table of Contents
Summary

The value() method is used to obtain a single value from the XML document data.

Description

The value() method performs an XQuery against the XML and returns a value of SQL type. This method returns a scalar value.

Note

The value() method works like the query() method to obtain the value and then performs the type conversion.

Operations
Obtain the value from a XML column or variable and expose it as a non-XML type value (generic)
How to do it

Template

SELECT xdoc.Value(‘XQUERY-expression’, ‘TYPENAME’)
[FROM FROM-CLAUSE
WHERE WHERE-CLAUSE]

Code sample

SELECT xdoc.Value(‘/root/product[@Id=”304”])[1]', 'int') [FROM __t__Table1]

 

Using the value() method against an XMLtype variable
How to do it
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
1
Using the value() method to retrieve a value from an XML type column
How to do it
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
35
34
28
25
23
19
Using the value() and exist() methods to retrieve values from an XML type column
How to do it
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
19
23
25
28
34
35