Partilhar via

sql:variable() Function (XQuery)

  • Artigo

Exposes a variable that contains a SQL relational value inside an XML data instance.

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 XML.

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.

In SQL Server 2005, 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

Reference

SQL Server XQuery Extension Functions

Concepts

Typed vs. Untyped XML
xml Data Type
Generating XML Instances
XML Data Modification Language (XML DML)

Other Resources

xml Data Type Methods

Help and Information

Getting SQL Server 2005 Assistance