{"id":1253,"date":"2011-12-08T00:00:00","date_gmt":"2011-12-08T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-xml-methods-in-sql-server\/"},"modified":"2026-04-30T09:21:08","modified_gmt":"2026-04-30T09:21:08","slug":"the-xml-methods-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/the-xml-methods-in-sql-server\/","title":{"rendered":"SQL Server XML Methods: query(), value(), exist(), nodes(), modify()"},"content":{"rendered":"
\n

SQL Server’s XML data type supports five built-in methods for working with XML content: query() returns a subset of XML matching an XQuery expression; value() returns a scalar value from a specific XML node converted to a T-SQL data type; exist() returns 1 or 0 indicating whether a node or value exists; nodes() shreds XML into a relational rowset that can be joined or aggregated; modify() updates XML in place with insert, replace, or delete operations. <\/strong><\/p>\n

All five methods take an XQuery expression as their argument; value() also takes a T-SQL type, and nodes() also takes a table alias. This article demonstrates each method with a working example against a sample Stores table containing both typed and untyped XML columns.<\/strong><\/p>\n

In my last article, “Working with the XML Data Type in SQL Server<\/a>,” I included examples that returned data from columns and variables of the XML<\/code><\/b> data type. Based on those examples, it might seem that retrieving XML data is similar to retrieving objects configured with other data types. However, that’s true only when returning the entire XML value. If instead you want to work with individual components within an XML instance, such as \u00a0when retrieving the value of a single attribute, you must use one of the five methods that available to the XML<\/code><\/b> data type-query()<\/code><\/b>, value()<\/code><\/b>, exist()<\/code><\/b>, nodes()<\/code><\/b>, or modify()<\/code><\/b>.<\/p>\n

To use an XML<\/code><\/b> method, you call it in association with the database object configured with the XML<\/code><\/b> data type, as the following syntax illustrates:<\/p>\n

DbObject.XmlMethod('XQuery'[, 'SqlType']) [AS TableAlias(ColumnAlias)]\n<\/pre>\n
The DbObject <\/i>placeholder refers to a column, variable, or parameter configured with the XML<\/code><\/b> data type. After the object name, you add a period, following by the name of the XML<\/code><\/b> method. Next you provide, in parentheses, the arguments required for the specific method.<\/p>\n
Most of the XML<\/code><\/b> methods require only one argument: an XQuery expression that identifies the XML components to be retrieved or modified, and any actions to be taken on those components. XQuery, a powerful scripting language used specifically to access XML data, contains the elements necessary to create complex expressions that can include functions, operators, variables, and values. MSDN provides a complete XQuery language reference<\/a> that you can refer to while you work with the XML<\/code><\/b> methods.<\/p>\n
In addition to the XQuery expression, an XML<\/code><\/b> method might also require a Transact-SQL data type as a second argument or a table and column alias tagged on after the arguments. As we work through the article, you’ll see when and why these additional components are necessary.<\/p>\n
To demonstrate how the various XML<\/code><\/b> methods work, I’ve written a number of examples that use the methods to retrieve and modify data. The examples are based on the Stores<\/code><\/b> table, which I created and populated with the following Transact-SQL script:<\/p>\n
USE AdventureWorks2008R2\nGO\n\u00a0\nIF OBJECT_ID('Stores') IS NOT NULL\nDROP TABLE Stores\nGO\n\u00a0\nCREATE TABLE Stores\n(\n\u00a0 StoreID INT PRIMARY KEY,\n\u00a0 Survey_untyped XML,\n\u00a0 Survey_typed XML(Sales.StoreSurveySchemaCollection)\n);\n\u00a0\n\u00a0INSERT INTO Stores\n\u00a0VALUES\n\u00a0(\n\u00a0\u00a0 292,\n\u00a0 '<StoreSurvey>\n\u00a0\u00a0\u00a0 <AnnualSales>800000<\/AnnualSales>\n\u00a0\u00a0\u00a0 <AnnualRevenue>80000<\/AnnualRevenue>\n\u00a0\u00a0\u00a0 <BankName>United Security<\/BankName>\n\u00a0\u00a0\u00a0 <BusinessType>BM<\/BusinessType>\n\u00a0\u00a0\u00a0 <YearOpened>1996<\/YearOpened>\n\u00a0\u00a0\u00a0 <Specialty>Mountain<\/Specialty>\n\u00a0\u00a0\u00a0 <SquareFeet>21000<\/SquareFeet>\n\u00a0\u00a0\u00a0 <Brands>2<\/Brands>\n\u00a0\u00a0\u00a0 <Internet>ISDN<\/Internet>\n\u00a0\u00a0\u00a0 <NumberEmployees>13<\/NumberEmployees>\n\u00a0\u00a0\u00a0 <Products Type=\"Bikes\">\n\u00a0\u00a0\u00a0\u00a0\u00a0 <Product>Mountain<\/Product>\n\u00a0\u00a0\u00a0\u00a0\u00a0 <Product>Road<\/Product>\n\u00a0\u00a0\u00a0\u00a0\u00a0 <Product>Racing<\/Product>\n\u00a0\u00a0\u00a0 <\/Products>\n\u00a0\u00a0\u00a0 <Products Type=\"Clothes\">\n\u00a0\u00a0\u00a0\u00a0\u00a0 <Product>Jerseys<\/Product>\n\u00a0\u00a0\u00a0\u00a0\u00a0 <Product>Jackets<\/Product>\n\u00a0\u00a0\u00a0\u00a0\u00a0 <Product>Shorts<\/Product>\n\u00a0\u00a0\u00a0 <\/Products>\n\u00a0 <\/StoreSurvey>',\n\u00a0 (SELECT Demographics FROM Sales.Store\n\u00a0\u00a0 WHERE BusinessEntityID = 292)\n);\n\u00a0\n<\/pre>\n
Notice that the table includes the Survey_untyped<\/code><\/b> column, which is an XML<\/code><\/b> untyped column, and the Survey_typed<\/code><\/b> column, which is an XML<\/code><\/b> typed column. The schema collection I associated with the typed column is already included in the AdventureWorks2008R2<\/code><\/b> database, which is where I’ve created the table. The INSERT<\/code><\/b> statement in the Transact-SQL above also shows the XML document that I added to the untyped column. For the typed column, I retrieved the following XML document from the Demographics<\/code><\/b> column (a typed XML<\/code><\/b> column) in the Sales.Store<\/code><\/b> table in the AdventureWorks2008R2<\/code><\/b> database:<\/p>\n
<StoreSurvey xmlns=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey\">\n\u00a0 <AnnualSales>800000<\/AnnualSales>\n\u00a0 <AnnualRevenue>80000<\/AnnualRevenue>\n\u00a0 <BankName>United Security<\/BankName>\n\u00a0 <BusinessType>BM<\/BusinessType>\n\u00a0 <YearOpened>1996<\/YearOpened>\n\u00a0 <Specialty>Mountain<\/Specialty>\n\u00a0 <SquareFeet>21000<\/SquareFeet>\n\u00a0 <Brands>2<\/Brands>\n\u00a0 <Internet>ISDN<\/Internet>\n\u00a0 <NumberEmployees>13<\/NumberEmployees>\n<\/StoreSurvey>\n\u00a0\n<\/pre>\n
Notice that the XML includes the schema associated with that data. The schema comes from the same schema collection I used when I created the typed column in the Stores<\/code><\/b> table.<\/p>\n
The data I added to the untyped column is nearly identical to the typed XML. The untyped XML doesn’t include the namespace information, of course. However, it does include additional product information, which we’ll use to demonstrate the XML<\/code><\/b> methods. So now that we have setup out of the way, let’s look at how those methods work.<\/p>\n
The query() method<\/h1>\n
The query()<\/code><\/b> method retrieves a subset of untyped XML from the target XML instance. It’s probably the simplest and most straightforward of the XML methods. You need only specify the database object, the method name, and an XQuery expression, as shown in the following syntax:<\/p>\n
DbObject.query('XQuery')\n<\/pre>\n
You’ll notice that the XQuery expression is entirely enclosed in single quotes and those are enclosed in parentheses. The trick with any XML method is to get the XQuery expression right. But at its simplest, the expression must define the path of the element you want to retrieve.<\/p>\n
In the following example, I use the query()<\/code><\/b> method to return data from the <StoreSurvey><\/code><\/b> element in both the typed and untyped columns:<\/p>\n
SELECT\n\u00a0 Survey_untyped.query('\/StoreSurvey') \n\u00a0\u00a0\u00a0 AS Info_untyped,\n\u00a0 Survey_typed.query('declare namespace \n\u00a0\u00a0\u00a0 ns=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey\";\n\u00a0\u00a0\u00a0 \/ns:StoreSurvey') AS Info_typed\nFROM\n\u00a0 Stores;\n\u00a0\n<\/pre>\n
For the untyped column, I specified the column name (Survey_untyped<\/code><\/b>) followed by a period and then the method name (query<\/code><\/b>). Within the parentheses and single quotes, I defined the XQuery expression (\/StoreSurvey<\/code><\/b>). That’s all there is to it. Because the <StoreSurvey><\/code><\/b> element is the root node, the entire XML document is returned.<\/p>\n
The XQuery expression for the Survey_typed<\/code><\/b> column is a bit more complex. Because it is a typed column, the expression should be preceded by a namespace declaration. The namespace must be the same as the one referenced within the XML document stored in the typed column.<\/p>\n
To declare a namespace, you specify the declare namespace<\/code><\/b> keywords, followed by the alias name (in this case, ns<\/code><\/b>). After the alias, you include an equal sign (=<\/code><\/b>) and then the schema path and name. You then end the declaration with a semi-colon (;<\/code><\/b>) to separate it from the main body of the XQuery expression. After you declare the namespace, you can use the alias within the expression to reference that namespace.<\/p>\n
The expression itself is nearly identical to the one used for the untyped column, except that you must precede the element name with the namespace alias and a colon (ns:<\/code><\/b>). As with the untyped column, the expression will return the entire XML document because it specifies only the root node.<\/p>\n
Although the preceding example is helpful in demonstrating the basics of using the query()<\/code><\/b> method, it’s not much use beyond that because you can just as easily retrieve all the column contents simply by specifying the column name. However, in the following example, I get more specific by limiting the results to the <AnnualSales><\/code><\/b> child element:<\/p>\n
SELECT\n\u00a0 Survey_untyped.query('\/StoreSurvey\/AnnualSales') \n\u00a0\u00a0\u00a0 AS Info_untyped,\n\u00a0 Survey_typed.query('declare namespace \n\u00a0\u00a0\u00a0 ns=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey\";\n\u00a0\u00a0\u00a0 \/ns:StoreSurvey\/ns:AnnualSales') AS Info_typed\nFROM\n\u00a0 Stores;\n\u00a0\n<\/pre>\n
The only difference between this example and the preceding one is that I added \/AnnualSales<\/code><\/b> to the XQuery expression for the untyped column and \/ns:AnnualSales<\/code><\/b> to the expression for the typed column. The XQuery for the untyped column returns the following results.<\/p>\n
<AnnualSales>800000<\/AnnualSales>\n<\/pre>\n
And the XQuery for the typed column returns these results:<\/p>\n
<ns:AnnualSales xmlns:ns=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey\">800000<\/ns:AnnualSales>\n<\/pre>\n
The primary difference between the two is that the data returned by the typed column includes the namespace information. The element values themselves (800000) are the same in both columns. If the <AnnualSales><\/code><\/b> element had included its own child elements, those too would have been displayed. For instance, the following example retrieves the <Products><\/code><\/b> elements, which are child elements of <StoreSurvey><\/code><\/b>, just like <AnnualSales><\/code><\/b>:<\/p>\n
SELECT\n\u00a0 Survey_untyped.query('\/StoreSurvey\/Products') \n\u00a0\u00a0\u00a0 AS Products\nFROM\n\u00a0 Stores;\n\u00a0\n<\/pre>\n
Because the XML document includes two <Products><\/code><\/b> elements and those elements each include several <Product> <\/code><\/b>child elements, the SELECT<\/code><\/b> statement returns all product-related elements, as shown in the following results:<\/p>\n
<Products Type=\"Bikes\">\n\u00a0 <Product>Mountain<\/Product>\n\u00a0 <Product>Road<\/Product>\n\u00a0 <Product>Racing<\/Product>\n<\/Products>\n<Products Type=\"Clothes\">\n\u00a0 <Product>jerseys<\/Product>\n\u00a0 <Product>jackets<\/Product>\n\u00a0 <Product>shorts<\/Product>\n<\/Products>\n<\/pre>\n
If you want to return a specific element and its child elements, you can do so by referencing one of its attributes. For example, suppose you want to include only products from the Bikes category. To do so, you can modify your XQuery expression as follows:<\/p>\n
SELECT\n\u00a0 Survey_untyped.query('\/StoreSurvey\/Products[@Type=\"Bikes\"]') \n\u00a0\u00a0\u00a0 AS BikeProducts\nFROM\n\u00a0 Stores;\n<\/pre>\n
Now the XQuery expression includes bracketed information that specifies the Type<\/code><\/b> attribute and its value. The attribute name is preceded by the at (@<\/code><\/b>) symbol, and the attribute value is preceded by an equal sign (=<\/code><\/b>) and enclosed in double quotes. As a result, the SELECT<\/code><\/b> statement now returns the following XML fragment:<\/p>\n
<Products Type=\"Bikes\">\n\u00a0 <Product>Mountain<\/Product>\n\u00a0 <Product>Road<\/Product>\n\u00a0 <Product>Racing<\/Product>\n<\/Products>\n\u00a0\n<\/pre>\n
As you would expect, only elements whose products are in the Bikes category are returned. You could have just as easily specified “Clothes” rather than “Bikes,” and you would have received the product elements related to clothing.<\/p>\n
The value() method<\/h1>\n
The value()<\/code><\/b> method returns a scalar value from the targeted XML document. The returned value is converted to the data type you specify when you call the method. The value()<\/code><\/b> method makes it easier to work with individual values in order to compare or combine them, either with other XML values or values of different types.<\/p>\n
As the following syntax illustrates, when you call the value()<\/code><\/b> method, you must specify the XQuery expression and the Transact-SQL data type for the returned data:<\/p>\n
DbObject.value('XQuery', 'SqlType')\n<\/pre>\n
For example, suppose you want to pull the amount of sales from the <AnnualSales><\/code><\/b> element in either the typed or untyped column in the Stores<\/code><\/b> table. You can specify the path in the XQuery expression plus a numerical data type as the second argument, as shown in the following SELECT<\/code><\/b> statement:<\/p>\n
SELECT\n\u00a0 Survey_untyped.value('(\/StoreSurvey\/AnnualSales)[1]', 'int') \n\u00a0\u00a0\u00a0 AS Sales_untyped,\n\u00a0 Survey_typed.value('declare namespace \n\u00a0\u00a0\u00a0 ns=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey\";\n\u00a0\u00a0\u00a0 (\/ns:StoreSurvey\/ns:AnnualSales)[1]', 'int') AS Sales_typed\nFROM\n\u00a0 Stores;\n\u00a0\n<\/pre>\n
For the untyped column, the XQuery expression includes the element path as you saw in earlier examples. However, after the path, you must add an integer in brackets that indicates which element of that name you should retrieve. More often than not, you will simply add [1]<\/code><\/b>, as I’ve done here. This assures that, if there are multiple elements with the same name, only the first one will be returned. It also assures that only one element is being referenced at a time. In fact, even when there is only one element, as is the case with <AnnualSales><\/code><\/b>, you must still include the [1]<\/code><\/b> because the value()<\/code><\/b> method expects a singleton value.<\/p>\n
The second argument passed into the value()<\/code><\/b> method is the name of the data type, in this case, int<\/code><\/b>. That means an int<\/code><\/b> value will be returned by the method. Note that the method’s two arguments must each be enclosed in single quotes and separated with a comma.<\/p>\n
As for the typed column, the namespace declaration and path are also similar to what you’ve seen in earlier examples. The only difference is that this expression must also include the [1]<\/code><\/b>, just as we did it for the untyped column. As a result, for each column, the SELECT<\/code><\/b> statement returns a single int<\/code><\/b> value of 800000<\/code><\/b>, without any additional element information.<\/p>\n
You can also retrieve an attribute value, rather than an element value, by specifying the name of the attribute in your element path. For example, the following SELECT<\/code><\/b> statement retrieves the value of the Type<\/code><\/b> attribute for the second instance of the <Products><\/code><\/b> element:<\/p>\n
SELECT\n\u00a0 Survey_untyped.value('(\/StoreSurvey\/Products\/@Type)[2]', 'varchar(10)')\n\u00a0\u00a0\u00a0 AS ProductType\nFROM\n\u00a0 Stores;\n\u00a0\n<\/pre>\n
The expression includes [2]<\/code><\/b>, rather than [1]<\/code><\/b>, in order to retrieve data from the second instance of <Products><\/code><\/b>. As a result, the SELECT<\/code><\/b> statement now returns the varchar<\/code><\/b> value Clothes<\/code><\/b>.<\/p>\n
As stated earlier, the value()<\/code><\/b> method is also handy if you want to combine or compare data. In the following example, I create a calculated column that’s based on two values returned from the untyped column:<\/p>\n
SELECT\n\u00a0 Survey_untyped.value('(\/StoreSurvey\/AnnualSales)[1]', 'int') -\n\u00a0 Survey_untyped.value('(\/StoreSurvey\/AnnualRevenue)[1]', 'int')\n\u00a0\u00a0\u00a0 AS Expenses\nFROM\n\u00a0 Stores;\n\u00a0\n<\/pre>\n
The first instance of value()<\/code><\/b> retrieves the <AnnualSales><\/code><\/b> value. The second value()<\/code><\/b> instance retrieves the <AnnualRevenue><\/code><\/b> value. The second value is then subtracted from the first value to return a scalar value of 720000<\/code><\/b>.<\/p>\n
You can also use XQuery functions in your expressions. For instance, in the following example, I use the concat<\/code><\/b> function to add a string to the <Specialty><\/code><\/b> value:<\/p>\n
SELECT\n\u00a0 Survey_untyped.value('concat(\"Bike specialty: \",\n\u00a0\u00a0 (\/StoreSurvey\/Specialty)[1])', 'varchar(25)') \n\u00a0\u00a0 AS Specialty\nFROM\n\u00a0 Stores;\n\u00a0\n<\/pre>\n
When you use the concat<\/code><\/b> function, you specify each element that you want to concatenate as an argument to the function and separate those arguments with a comma. The statement returns the value Bike specialty: Mountain<\/code><\/b>.<\/p>\n
As you can see, the string has been concatenated with the <Specialty><\/code><\/b> value. I could have just as easily concatenated multiple element values or added more string values.<\/p>\n
The exist() method<\/h1>\n
The exist()<\/code><\/b> method lets you test for the existence of an element or one of its values. The method takes only one argument, the XQuery expression, as shown in the following syntax:<\/p>\n
DbObject.exist('XQuery')\n<\/pre>\n
The key to using the exist()<\/code><\/b> method is in understanding the values it returns. Unlike the query() <\/code><\/b>and value()<\/code><\/b> methods, the exist()<\/code><\/b> method doesn’t return XML content. Rather, the method returns one of the following three values:<\/p>\n