| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | dev_langs | |||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Binding Relational Data Inside XML Data |
Binding Relational Data Inside XML Data |
MikeRayMSFT |
mikeray |
07/26/2017 |
sql |
t-sql |
reference |
|
|
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]
You can specify xml Data Type Methods against an xml data type variable or column. For example, the query() Method (xml Data Type) executes the specified XQuery against an XML instance. When you construct XML in this manner, you may want to bring in a value from a non-XML type column or a Transact-SQL variable. This process is referred to as binding relational data inside XML.
To bind the non-XML relational data inside XML, the SQL Server Database Engine provides the following pseudo-functions:
-
sql:column() Function (XQuery) Lets you use the values from a relational column in your XQuery or XML DML expression.
-
sql:variable() Function (XQuery) . Lets you use the value of a SQL variable in your XQuery or XML DML expression.
You can use these functions with xml data type methods whenever you want to expose a relational value inside XML.
You cannot use these functions to reference data in columns or variables of the xml, CLR user-defined types, datetime, smalldatetime, text, ntext, sql_variant, and image types.
Also, this binding is for read-only purposes. That is, you cannot write data in columns that use these functions. For example, sql:variable("@x")="some expression" is not allowed.
This example shows how sql:variable() can enable an application to parameterize a query. The ISBN is passed in by using a SQL variable @isbn. By replacing the constant with sql:variable(), the query can be used to search for any ISBN and not just the one whose ISBN is 0-7356-1588-2.
DECLARE @isbn VARCHAR(20)
SET @isbn = '0-7356-1588-2'
SELECT xCol
FROM T
WHERE xCol.exist ('/book/@ISBN[. = sql:variable("@isbn")]') = 1 sql:column() can be used in a similar manner and provides additional benefits. Indexes over the column may be used for efficiency, as decided by the cost-based query optimizer. Also, the computed column may store a promoted property.