tailieunhanh - Microsoft SQL Server 2008 R2 Unleashed- P192

Microsoft SQL Server 2008 R2 Unleashed- P192:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1904 CHAPTER 47 Using XML in SQL Server 2008 for outer in outernode inner in outer innernode return Outside letter outer @name Inside number inner Outside go Outside letter a Inside number 1 Outside Outside letter a Inside number 2 Outside Outside letter a Inside number 3 Outside Outside letter b Inside number 4 Outside Outside letter b Inside number 5 Outside Outside letter b Inside number 6 Outside The let Clause New to SQL Server 2008 the let clause is the L in FLWOR. It performs the critical function of enabling variable value assignments in XQuery expressions. Here s an example WITH XMLNAMESPACES DEFAULT http sqlserver 2004 07 adventure- works StoreSurvey SELECT TOP 1 for N in StoreSurvey let Total N AnnualRevenue N AnnualSales order by N AnnualSales return Statement AnnualRevenue N AnnualRevenue Using the xml Data Type 1905 AnnualSales N AnnualSales SalesPlusRevenue Total FROM WHERE AnnualRevenue xs integer . 300000 1 The where Clause Just like the WHERE clause in T-SQL XQuery s where clause restricts the nodes in the selected node list to those matching a certain expression. Here s an example SELECT TOP 1 declare namespace ns http sqlserver 2004 07 adventure-works Resume for ResumeNode in ns Resume where count ResumeNode ns Employment 2 return ResumeNode ns Employment ns FROM go ns xmlns ns http sqlserver 2004 07 adventure- works Resume Lead Machinist ns ns xmlns ns http sqlserver 2004 07 adventure- works Resume Machinist ns ns xmlns ns http sqlserver 2004 07 adventure- works Resume Assistant Machinist ns Here you use the T-SQL-analogous count aggregate function to restrict the result set to ns Resume nodes having three or more ns Employment children. The standard .