sql:column() Function (XQuery)

As described in the topic, Binding Relational Data Inside XML, you can use the sql:column(() function when you use XML Data Type Methods to expose a relational value inside XML.

For example, the query() method (XML data type) is used to specify a query against an XML instance that is stored in a variable or column of xml type. Sometimes, you may also want your query to use values from another, non-XML column, to bring relational and XML data together. To do this, you use the sql:column() 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.

Syntax

sql:column("columnName")

Remarks

Note that reference to a non-XML column specified in the sql:column() function inside an XQuery refers to a column in the row that is being processed.

In SQL Server 2005, you cannot refer to columns that are of type xml or a CLR user-defined type.

The sql:column() function is not supported in JOIN operations. The APPLY operation can be used instead.

Examples

A. Using sql:column() to retrieve the relational value inside XML

In constructing XML, the following example illustrates how you can retrieve values from a non-XML relational column to bind XML and relational data.

The query constructs XML that has the following form:

<Product ProductID="771" ProductName="Mountain-100 Silver, 38" ProductPrice="3399.99" ProductModelID="19" 
  ProductModelName="Mountain 100" />

Note the following in the constructed XML:

• The ProductID, ProductName, and ProductPrice attribute values are obtained from the Product table.

• The ProductModelID attribute value is retrieved from the ProductModel table.

• To make the query more interesting, the ProductModelName attribute value is obtained from the CatalogDescription column of xml type. Because the XML product model catalog information is not stored for all the product models, the if statement is used to retrieve the value only if it exists.

  SELECT P.ProductID, CatalogDescription.query('
  declare namespace pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
         <Product 
             ProductID=       "{ sql:column("P.ProductID") }"
             ProductName=     "{ sql:column("P.Name") }"
             ProductPrice=    "{ sql:column("P.ListPrice") }"
             ProductModelID= "{ sql:column("PM.ProductModelID") }" >
             { if (not(empty(/pd:ProductDescription))) then
               attribute ProductModelName { /pd:ProductDescription[1]/@ProductModelName }
              else 
                 ()
  }
          </Product>
  ') as Result
  FROM Production.ProductModel PM, Production.Product P
  WHERE PM.ProductModelID = P.ProductModelID
  AND   CatalogDescription is not NULL
  ORDER By PM.ProductModelID
  

Note the following from the previous query:

• Because the values are retrieved from two different tables, the FROM clause specifies two tables. The condition in the WHERE clause filters the result and retrieves only products whose product models have catalog descriptions.
• The namespace keyword in the XQuery Prolog defines the XML namespace prefix, "pd", that is used in the query body. Note that the table aliases, "P" and "PM", are defined in the FROM clause of the query itself.
• The sql:column() function is used to bring non-XML values inside XML.

This is the partial result:

ProductID               Result
-----------------------------------------------------------------
771         <Product ProductID="771"                   ProductName="Mountain-100 Silver, 38" 
                  ProductPrice="3399.99" ProductModelID="19" 
                  ProductModelName="Mountain 100" />
...

The following query constructs XML that contains product-specific information. This information includes the ProductID, ProductName, ProductPrice, and, if available, the ProductModelName for all products that belong to a specific product model, ProductModelID=19. The XML is then assigned to the @x variable of xml type.

declare @x xml
SELECT @x = CatalogDescription.query('
declare namespace pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
       <Product 
           ProductID=       "{ sql:column("P.ProductID") }"
           ProductName=     "{ sql:column("P.Name") }"
           ProductPrice=    "{ sql:column("P.ListPrice") }"
           ProductModelID= "{ sql:column("PM.ProductModelID") }" >
           { if (not(empty(/pd:ProductDescription))) then
             attribute ProductModelName { /pd:ProductDescription[1]/@ProductModelName }
            else 
               ()
}
        </Product>
') 
FROM Production.ProductModel PM, Production.Product P
WHERE PM.ProductModelID = P.ProductModelID
And P.ProductModelID = 19
select @x

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