SQL Server - Value Method

The value() method accepts two arguments. The first argument is a string XQuery expression, and the second argument is a string containing a SQL Server data type. The value() method extracts a single value using the XPath expression and returns it as the specified type. For example, in this scenario, DBA/Developer could load the XML into an xml variable named @x and use the following statement to insert rows into a temporary table:

SELECT

t.c.value('@ID[1]', 'int') AS StationID, t.c.value('@Type[1]', 'varchar(35)')
AS ProcessType, t.c.value('PieceCount[1]', 'int') AS NumPieces
INTO #PieceCounts
FROM @x.nodes('//Root/Station')
AS t(c);

With this statement, the value() method extracts values of the ID and Type attributes of the <Station> element and the value of the <PieceCount> element. Each element or attribute specified is returned with the specified data type, and the INTO clause inserts the result into a temporary table named #PieceCounts. The [1] at the end of each string expression indicates that the information is extracted from a single element. The value() method must return a single value. However, in this scenario, the XML contains multiple <Station> elements. Therefore, the nodes() method must be used in the FROM clause. With the given statement, the following data would be inserted into the #PieceCounts table. DBA/Developer should not use the query() method. The query() method is used to query and retrieve XML elements and attributes from an XML instance as untyped XML, not a relational result set. The method accepts a string XQuery expression that determines which elements and element attributes are extracted.DBA/Developer should not use the nodes() method. The nodes() method accepts an XQuery string and returns all of the specified nodes as a result set.

DBA/Developer can use the nodes() method in the FROM clause of a query to process multiple XML nodes using the value() method in the SELECT list.DBA/Developer should not use the modify() method. The modify() method is used to change the value of an xml type variable or column. The modify() method can only be invoked using the SET clause of an UPDATE statement. The method accepts a string argument containing an XML Data Manipulation Language (DML) statement, such as replace value of, insert, or delete. The statement is then used to update the corresponding element or attribute in the XML.