Edit

Share via

XQuery Extension Functions - sql:variable()

  • Article

Applies to: SQL Server

Exposes a variable that contains a SQL relational value inside an XQuery expression.

Syntax

  
sql:variable("variableName") as xdt:anyAtomicType?

Remarks

As described in the topic Binding Relational Data Inside XML, you can use this function when you use XML data type methods to expose a relational value inside XQuery.

For example, the query() method is used to specify a query against an XML instance that is stored in an xml data type variable or column. Sometimes, you might also want your query to use values from a Transact-SQL variable, or parameter, to bring relational and XML data together. To do this, you use the sql:variable function.

The SQL value will be mapped to a corresponding XQuery value and its type will be an XQuery base type that is equivalent to the corresponding SQL type.

You can only refer to an xml instance in the context of the source expression of an XML-DML insert statement; otherwise you cannot refer to values that are of type xml or a common language runtime (CLR) user-defined type.

Examples

A. Using the sql:variable() function to bring a Transact-SQL variable value into XML

The following example constructs an XML instance that made up of the following:

  • A value (ProductID) from a non-XML column. The sql:column() function is used to bind this value in the XML.

  • A value (ListPrice) from a non-XML column from another table. Again, sql:column() is used to bind this value in the XML.

  • A value (DiscountPrice) from a Transact-SQL variable. The sql:variable() method is used to bind this value into the XML.

  • A value (ProductModelName) from an xml type column, to make the query more interesting.

This is the query:

DECLARE @price money  
  
SET @price=2500.00  
SELECT ProductID, Production.ProductModel.ProductModelID,CatalogDescription.query('  
declare namespace pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
  
       <Product   
           ProductID="{ sql:column("Production.Product.ProductID") }"  
           ProductModelID= "{ sql:column("Production.Product.ProductModelID") }"  
           ProductModelName="{/pd:ProductDescription[1]/@ProductModelName }"  
           ListPrice="{ sql:column("Production.Product.ListPrice") }"  
           DiscountPrice="{ sql:variable("@price") }"  
        />')   
FROM Production.Product   
JOIN Production.ProductModel  
ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID  
WHERE ProductID=771

Note the following from the previous query:

  • The XQuery inside the query() method constructs the XML.

  • The namespace keyword is used to define a namespace prefix in the XQuery Prolog. This is done because the ProductModelName attribute value is retrieved from the CatalogDescription xml type column, which has a schema associated with it.

This is the result:

<Product ProductID="771" ProductModelID="19"   
         ProductModelName="Mountain 100"   
         ListPrice="3399.99" DiscountPrice="2500" />

See Also

SQL Server XQuery Extension Functions
Compare Typed XML to Untyped XML
XML Data (SQL Server)
Create Instances of XML Data
xml Data Type Methods
XML Data Modification Language (XML DML)