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="http://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" />