Specifying the nodes() method against a column of xml type In the following example result, the query method returns the context item and its content: Larrysome textĪpplying the parent accessor on the context nodes returns the element for all three: SELECT T.c.query('.') AS result The query then returns the context node from each row: DECLARE XML
Each row has a logical copy of the original XML, with each context node identifying a different element in the original document. The nodes() method returns a rowset with three rows. The query uses the nodes() method to set separate context nodes, one for each element. In the following example, there's an XML document that has a top-level element and three child elements. USE AdventureWorks Įxamples Using nodes() method against a variable of xml type The following example shows one way to use CROSS APPLY to select from the result of a user-defined function. Use a derived table to assign a column alias to the user-defined function return value and then use CROSS APPLY to select from the alias.Assign the result of the user-defined function to a variable.To use the nodes() function with the result of a scalar user-defined function, you can either: The nodes() function can't be applied directly to the results of a user-defined function. So, it must be explicitly named by using aliasing. The rowset returned by the nodes() method is an unnamed rowset. However, you don't have to use cursors when you use the nodes() method on a table that contains several rows of XML documents. Scenarios for using the nodes() method are the same as for using OPENXML (Transact-SQL), which provides a rowset view of the XML. However, you can use it in IS NULL and COUNT(*). That is, you can't use it in a SELECT statement. However, you can't apply the modify() method to modify the XML instance.Īlso, the context node in the rowset can't be materialized. You can apply xml data type methods, such as query(), value(), exist(), and nodes(), to the result of a nodes() method. The rowset returned has maintained the type information. Here is the result: ProductModelID Instructions The following query extracts the subtree of the context item for each generated row: SELECT T2.Loc.query('.')ĬROSS APPLY Instructions.nodes('/root/Location') AS T2(Loc) You can then query this rowset by using xml data type methods. Ī nodes() method invocation with the query expression /root/Location would return a rowset with three rows, each containing a logical copy of the original XML document, and with the context item set to one of the nodes: Product
Notice that there are three manufacturing locations in the document. The following manufacturing instructions document is stored in the table. RemarksĪs an example, assume that you have the following table: T (ProductModelID INT, Instructions XML) Is the table name and the column name for the resulting rowset. If the query expression statically results in a sequence that contains atomic values instead of nodes, a static error is raised. If the query expression results in an empty sequence, the rowset is empty as well. If the query expression constructs nodes, these constructed nodes are exposed in the resulting rowset. Is a string literal, an XQuery expression. To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation. The value() method, when applied to the XML instance, returns only one value.
For example, you can apply the value() method to the rowset returned by nodes() and retrieve multiple values from the original XML instance. You can retrieve multiple values from the rowset. This way, later queries can navigate relative to these context nodes. In these logical copies, the context node of every row instance is set to one of the nodes that is identified with the query expression. The result of the nodes() method is a rowset that contains logical copies of the original XML instances. The document node is the implicit node at the top of every xml data type instance. For the XML instance stored in a column or variable, this node is the document node. It allows you to identify nodes that will be mapped into a new row.Įvery xml data type instance has an implicitly provided context node. The nodes() method is useful when you want to shred an xml data type instance into relational data. Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance
0 kommentar(er)
