Quick - TSQL select XPath from XML Data Type with namespace
/
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.