Let's take a look at the following example from Books Online:

DECLARE @price money

SET @price=2500.00
SELECT CatalogDescription.query('
declare namespace pd="

           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

-- Output

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

What do we have here:

  1. The xml result is constructed using the query() method and XQuery language.
  2. The value for ProductID is obtained from a non-XML column using the sql:column() function to bind this value in the XML.
  3. The same method is used for the value of ListPrice from a non-XML column of another table.
  4. The value of DiscountPrice is taken from a Transact-SQL variable using the sql:variable() function.
  5. Last the value of ProductModelName is taken from an xml type column.

If you intent to take the 70-442 you should be familiar with all the above methods and functions: query(), sql:column(), sql:variable().