Execute the following SQL Server T-SQL example scripts in Management Studio Query Editor to demonstrate the xml value method.

-- List all banks used by bike stores

USE AdventureWorks;

-- XML value method

SELECT distinct Demographics.value('declare namespace

AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";

(AWMI:StoreSurvey/AWMI:BankName)[1]','varchar(30)')

as [Store Banks]

FROM Sales.Store

GO

/* Results

Store Banks

Guardian Bank

International Bank

International Security

Primary Bank & Reserve

Primary International

Reserve Security

United Security

*/

------------

-- List sales order ids and total order amounts

USE AdventureWorks

GO

DECLARE  @xml XML

SELECT @xml = (SELECT SalesOrderID,

                      SubTotal

               FROM   Sales.SalesOrderHeader

               FOR XML RAW('Row'),ROOT('Rows'),ELEMENTS)

— XML value method

SELECT   SalesOrderID = list.SO.value('SalesOrderID[1]','nvarchar(10)'),

         TotalDollar = list.SO.value('SubTotal[1]','nvarchar(16)')

FROM     @xml.nodes('/Rows/Row') AS list (SO)

ORDER BY SalesOrderID DESC

GO

/* Partial results

SalesOrderID      TotalDollar

75123             189.9700

75122             30.9700

75121             74.9800

75120             84.9600

75119             42.2800

75118             135.2300

*/

------------