Examples on MSDN shows the full TSQL syntax for XPath query in a TSQL statement, this has been supported since SQL Server 2008.

SELECT CatalogDescription.query(' declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; <Product ProductModelID="{ /PD:ProductDescription[1]/@ProductModelID }" /> ') as Result FROM Production.ProductModel

This returns:

<Product ProductModelID="19"/>
<Product ProductModelID="23"/>

Wildcard in XPath Query

This is a much easier syntax with Wildcards that isn't well know.

SELECT CatalogDescription.query('/*:ProductDescription[1]/@ProductModelID') as Result
FROM Production.ProductModel

This returns:

19
23

Plenty of people will tell you using Wildcard matches in XPath is the worst thing in the world.  But I think if you just want to do a quick query, this is fine.