xquery query path without elements

lkh

The Demographics xml data type column in AdventureWorks.Sales.Store contains the row sample below.

How would I write the path for Demographics.query() to retun all rows with AnnualSales >=300000

I'm using variations of this statement but can't find an example of a path without elements.

SELECT demographics.query('declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";

/ ') as Result

FROM Sales.Store

<StoreSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey">

<AnnualSales>300000</AnnualSales>

<AnnualRevenue>30000</AnnualRevenue>

<BankName>International Bank</BankName>

<BusinessType>BM</BusinessType>

<YearOpened>1970</YearOpened>

<Specialty>Road</Specialty>

<SquareFeet>7000</SquareFeet>

<Brands>AW</Brands>

<Internet>T1</Internet>

<NumberEmployees>2</NumberEmployees>

</StoreSurvey>

lkh

This seems to do the trick....now I need to filter out rows where Result IS NOT NULL

SELECT Name, demographics.query('declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";

for $WC in /AWMItoreSurvey

where $WC/AWMI:AnnualSales ge 800001

return

string($WC/AWMI:AnnualSales)') As Result

FROM Sales.Store

lkh

Adding this WHERE clause works. Is this the simplest way to filter the Sales.Store with an attribute in the demographics column

SELECT a.Name, a.Result

FROM

(

SELECT Name, demographics.query('declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";

for $WC in /AWMItoreSurvey

where $WC/AWMI:AnnualSales ge 800001

return

string($WC/AWMI:AnnualSales)') As result

FROM Sales.Store

)a

WHERE CAST(a.Result AS nvarchar)<>''

Martin Honnen

Does the following query give you the result your are looking for

Code Snippet

SELECT Name, Demographics.query('declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";

string((AWMI:StoreSurvey/AWMI:AnnualSales)[1])') As Result

FROM Sales.Store

WHERE Demographics.exist('

declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";

/AWMI:StoreSurvey[AWMI:AnnualSales ge 800001]') = 1;

lkh

Thanks. That works. Do you have any recommendations for good XML books, that apply to SQL Server's implementation, other that the Wrox "Pro. SQL Server 2005 XML I'm finding I have to jump around a lot and experiment when using BOL.