Dela via

  • Artikel

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

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 column specified in the sql:column() function inside an XQuery refers to a column in the row that is being processed.

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