[原创]SharePoint结合Reporing Service，使用XQuery查询表单库Xml文件并输出报表

hjbhhj

源代码下载地址：
http://files.cnblogs.com/starcrm/SharePoint_XQuery_Reporting_Demo.rar
　　因为项目需要，需要研究这项技术，原因是当前公司有很多数据是保存在SharePoint中，通过Infopath采集数据，而InfoPath数据的存储不是全部域值都另存一份进数据库，相当多数据在XML文件中，这就造成了查询和打印报表的困难，亟需一种解决方案。
简单说明一下思路：
第一步：将SharePoint中的InfoPath XML文件后期（我做了一个小工具XMLToDB）或通过Event Handle同步保存到SQL Server表中的一个XML类型字段中；
第二步：通过XQuery查询；
第三步：通过VS.Net或者Reporting Builder进行报表设计；
第四步：通过Reporting Service进行报表输出。

因为是在外企工作，所以使用了英文，时间有限，就不翻译成中文了，见谅。
源代码也一并放出。

　　


源代码下载地址 (Source code download)：
http://files.cnblogs.com/starcrm/SharePoint_XQuery_Reporting_Demo.rar
　　
　　
　　








Introduction To SharePoint XQuery
Reporting Solution



1. Projects

1.1 XmlToDB

The
project use to export the SharePoint form library's
XML files to new database table, or update the XML files to existing database
field(XML data type). And it can save the associate XML nodes value to database
fields with easy configure.
Major files:
·        
App.config:
Configure file for control SharePoint site URL
/ DB Connection string / XML- Database mapping…
·        
Program.cs
: Console entry file for this project , Loop the SPListItem
and run
the function                                     Common.SaveXMLFileToDB();
·        
Common.cs
: Contain the function: SaveXMLFileToDB(SPListItem listItem) , there are two mode to save database:insert
and update,control by  ConfigurationManager.AppSettings["Mode"]

Code:
public static
void SaveXMLFileToDB(SPListItem listItem)
        {
           
Stream stream = null;
           
MemoryStream fileStream = null;
           
try
           
{
               
//throw new Exception("test");
               
SPFile file = listItem.File;
               
if (file != null)
               
{
                  
XmlDocument xDoc = new XmlDocument();
                  
byte[] bytes = file.OpenBinary();
                  
stream = new MemoryStream(bytes);
                  
xDoc.Load(stream);

                  
XmlNamespaceManager xNsMgr = new
XmlNamespaceManager(xDoc.NameTable);
                  
string strNamespace = Common.GetNamespace(file.Name);
                  
xNsMgr.AddNamespace("my",
strNamespace);
                  
SaveXML(xDoc, xNsMgr);
               
}

           
}
           
catch (Exception
ex)
           
{
               
WriteLog.LogErrMsg(ex);
         
     Console.WriteLine(ex.Message);
           
}
           
finally
           
{
               
if (stream != null)
                  
stream.Close();
               
if (fileStream != null)
                  
fileStream.Close();
           
}
        }

        public static void SaveXML(XmlDocument xDoc, XmlNamespaceManager
xNsMgr)
        {
           
string sql = "";
           
string xml = xDoc.InnerXml;
           
string DbTableName =
ConfigurationManager.AppSettings["DbTableName"];
            string keyColumn = ConfigurationManager.AppSettings["keyColumn"].Split(',')[0];
           
string keyValue =
xDoc.SelectSingleNode(ConfigurationManager.AppSettings["keyColumn"].Split(',')[1], xNsMgr).InnerText;
           
string XmlColumn = ConfigurationManager.AppSettings["XmlColumn"];

           
Dictionary<string,
string> dicColumnAssociate = new Dictionary<string, string>();
           
Dictionary<string,
object> valueAssociate = new Dictionary<string, object>();
           
string sColumnAssociate =
ConfigurationManager.AppSettings["ColumnAssociate"];
           
string[] arrColumnAssociate =
sColumnAssociate.Split(';');
           
for (int i = 0;
i < arrColumnAssociate.Length; i++)
           
{
               
dicColumnAssociate.Add(arrColumnAssociate.Split(',')[0],
arrColumnAssociate.Split(',')[1]);
               
valueAssociate.Add(arrColumnAssociate.Split(',')[0],
xDoc.SelectSingleNode(arrColumnAssociate.Split(',')[1],
xNsMgr).InnerText);
           
}

           
//Mode is insert
           
string mode = ConfigurationManager.AppSettings["Mode"];
           
if (mode == "insert")
           
{
               
sql = "INSERT INTO [{0}]";
               
sql += "([{1}]";

               
foreach (KeyValuePair<string, string>
kvp in dicColumnAssociate)
               
{
                  
sql += ",[" + kvp.Key + "]";
               
}

               
sql += ",[{2}]) VALUES ";

               
sql += "('{3}'";

               
foreach (KeyValuePair<string, object>
kvp in valueAssociate)
               
{
                  
sql += ",'" + kvp.Value.ToString()
+ "'";
               
}

               
sql += ",'{4}')";

               

               
sql = string.Format(sql, DbTableName,
keyColumn, XmlColumn, keyValue, xml);
               
if (Common.ExecSQLForNonQuery(sql) == 1)
               
{
                  
WriteLog.LogMsg(keyValue+" Exported");
                  
Console.WriteLine(keyValue + " Exported");
               
}

           
}
           
else if (mode
== "update")           
//Mode is update
           
{
               
//todo
           
}

      
}


Database and test form library introduction

Database
major field:
&#183;        
ProductName
&#183;        
XmlInfo: XML data type field, to store SharePoint form library's
XML file




DEMO:

Step1:design the demo infopath template:



Step2:Pulish template and fill some dome data:




Step3:Save the data to form library:



Step4: Prepare the demo datebase,



Step5:Config the XMLtoDB project



Step6: Run the XMLtoDB.exe program



Step7:Check database.  Xml has saved to database.




1.2 XQuery
Web Demo

Query the database XML type field.
Major files:
&#183;        
Default.aspx
: start page;
&#183;        
SearchElement.ascx
: user control for auto generate query conditions;
Demo:

Step1:Try to query xml field




Step2:Try XQuery. It is work!



1.3 XQuery
Report Project




Step1:Install
and config SQL Server Reporting Service






Step2:Open
.Net Visual Studio Create a new Reporting Service Project



Create
a new report item



Step3:Datasource
config



If
choose Database as datasource:


Note:
l
XQuery can&#8217;t refers to SQL Server View and Produce,So if use
Reporting Service with XQuery,Must not choose Database data source;
l
SharePoint data source is appoint a SharePoint List as data
source,Xml file nodes must map to list field,5 MC ,total more than 1000*5
fields,so SharePoint data source could not be choosed also.
l
So must choose Object or Service as data source, Service
return value must to convert to Entity Object,so in this demo the best choice
is Object as data source.


Step4:Create
business objects to use as a data source.


Business
Objects code here:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace XQueryReportDemo
{
   
public class Product
   
{
      
private string
_ProductID;

      
public string
ProductID
      
{
           
get { return
_ProductID; }
           
set { _ProductID = value;
}
      
}
      
private string
_ProductType;

      
public string
ProductType
      
{
           
get { return
_ProductType; }
           
set { _ProductType = value;
}
      
}
      
private string
_Entity;

      
public string
Entity
      
{
           
get { return
_Entity; }
           
set { _Entity = value;
}
      
}
      
private int
_CoveragePeriod;

      
public int
CoveragePeriod
      
{
           
get { return
_CoveragePeriod; }
           
set { _CoveragePeriod = value; }
      
}
      
private int
_PremiumPeriod;

      
public int
PremiumPeriod
      
{
           
get { return
_PremiumPeriod; }
           
set { _PremiumPeriod = value;
}
      
}
      
private int
_IssueAge;

      
public int
IssueAge
      
{
           
get { return _IssueAge;
}
           
set { _IssueAge = value;
}
      
}
      
private int
_AttainedAge;

      
public int
AttainedAge
      
{
           
get { return
_AttainedAge; }
           
set { _AttainedAge = value;
}
      
}

      
public Product()
      
{
      
}

      

   
}

   
public class AXAProducts
   
{
      
private List<Product> m_products;

      
public AXAProducts()
      
{
           

           
m_products = new List<Product>();
           
string sql = @"WITH
XMLNAMESPACES(
'http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-10-21T21:12:27'
AS ""my"") ";
           
sql += @"SELECT ";
           
sql += @"xmlinfo.value('(/my:meetingAgenda/my:ProductID)[1]','nvarchar(50)')
as ProductID,";
           
sql += @"xmlinfo.value('(/my:meetingAgenda/my:ProductType)[1]','nvarchar(50)')
as ProductType , ";
           
sql += @"xmlinfo.value('(/my:meetingAgenda/my:Entity)[1]','nvarchar(50)')
as Entity , ";
           
sql += @"xmlinfo.value('(/my:meetingAgenda/my:CoveragePeriod)[1]','int')
as CoveragePeriod ,";
           
sql += @"xmlinfo.value('(/my:meetingAgenda/my:PremiumPeriod)[1]','int')
as PremiumPeriod ,";
           
sql += @"xmlinfo.value('(/my:meetingAgenda/my:IssueAge)[1]','int')
as IssueAge , ";
           
sql += @"xmlinfo.value('(/my:meetingAgenda/my:AttainedAge)[1]','int')
as AttainedAge";
           
sql += @" FROM dbo.XQueryTable1";
           
DataSet ds = new
DataSet();
           
ds = DataCommon.GetDataSet(sql);
           
m_products = DataCommon.GetList<Product>(ds.Tables[0]);

      
}

      
public List<Product> GetProducts()
      
{
           
return m_products;
      
}
   
}

}



Step5:Add a
report to the project


In
the Add New Item dialog, select Report. Type a name for the report and click
Add. The report is added to the project and automatically opened in Report
Designer. The default name for the report is Report.rdlc.





Step6:Check
the Website Data Sources


1.Click
the Report.rdlc [Design] tab. In the left pane, click the Website Data Sources
tab. If the Website Data Sources tab is not visible, from the Data menu, select
Show Data Sources.

2.Confirm
that the Product object and its public properties, appear in a hierarchy in the
Website Data Sources window.



Step7:Design
the report

1.     
With
the report open in Design mode, open the Toolbox. From the Toolbox, drag a
Table control onto the report. The table control opens in a tabbed Design
window.



2.From
the Data Sources window, drag the field from the Product data source onto the
column of the Detail row of the table. The Detail row is the middle row. Notice
that the Header row automatically  fills in for you when you specify the
Detail row.

3.(Optional)
Select the header row of the table by clicking on the left table header icon
and apply the Bold font style.

5.To
add a title to the report, open the Toolbox and drag a Textbox onto the report.
Position the Textbox above the table. Type the report name.




Step8:Add a
ReportViewer control to the Web page




Step9:Run
the application



Find
some word:


Save
to PDF



Step10:Design
matrix report







Group:


Expression:


Child
group:



Step11:Run



Chart
report:







2.   
Create
Report By Report Builder


Report
Builder 3.0 is a report authoring environment for business users who prefer to
work in the Microsoft Office environment. When you design a report, you specify
where to get the data, which data to get, and how to display the data. When you
run the report, the report processor takes all the information you have
specified, retrieves the data, and combines it with the report layout to
generate the report. You can preview your reports in Report Builder, or you can
publish your report to a report server or a report server in SharePoint
integrated mode, where others can run it.
The
report in this illustration features a matrix with row and column groups,
indicators, and a summary pie chart in the corner cell, accompanied by a map
with two sets of geographic data represented by color and by circle size.














3.  XQuery Introduction



3.1 Methods of the XML Data Type
　　The XML data type supports five methods that can be used to
manipulate XML instances. The methods of the XML data type can be described as
follows:
　　&#183;        
The query() method takes an XQuery expression that evaluates to a list of XML
nodes and allows the user to extract fragments of an XML document. The result
of this method is an instance of untied XML.
　　&#183;        
The value() method is useful for extracting scalar values from XML documents as
a relational value. This method takes an XQuery expression that identifies a
single node and the desired SQL type to be returned. The value of the XML node
is returned cast to the specified SQL type.
　　&#183;        
The exist() method allows the user to perform checks on XML documents to
determine if the result of an XQuery expression is empty or nonempty. The
result of this method is 1 if the XQuery expression returns a nonempty result,
0 if the result is empty, and NULL if the XML instance itself is NULL.
　　&#183;        
The nodes() method accepts an XQuery expression and returns a rowset in which
each row represents a context node identified by the query expression. Methods
of the XML data type such as query(), value(), exist(), and nodes() can also be invoked on the context nodes returned from the nodes() method.
　　&#183;        
The modify() method can be used to modify the content of an XML document. It
accepts XML DML statements to insert, update, or delete one or more nodes from
an XML instance. It raises an error if applied to a NULL value.

3.2 XPath 2.0 Expressions
　　XQuery uses XPath 2.0 expressions to locate nodes in a
document and to navigate from one location to another within a single document
or across documents. Navigation paths defined using XPath consist of a sequence
of steps separated by /. A single step comprises an axis, a node test, and zero
or more step qualifiers.
　　The axis specifies the direction of movement, relative to the context node.
Supported axes in SQL Server 2005 are child, descendant,
parent, attribute, self and descendant-or-self.

　　This example expression uses the column Resume which is of
type XML in table [HumanResources].[JobCandidate] from the AdventureWorks
database for the purpose of illustrating the concept of path expressions. The
following path expression selects all address nodes for which the address type
is set to Home.

//child::ns:Addr.Type[.="Home"]/parent::node()
　　In the preceding path expression,

• child is the axis specifier.
  
• :: is the axis separator.
  
• ns is the namespace prefix.
  
• Addr.Type is the node test.
  
• [.="Home"] is the predicate expression where . refers to the context
  node.
  

　　XQuery also supports abbreviated syntax for specifying the
axis. The following table shows the axis and corresponding abbreviated syntax.
Table
2   Abbreviated syntax for axes

Axis	Abbreviated form
Attribute	@
Child
descendant-or-self::node()	//
parent::node()	..
self::node()	.

3.3 Query Example

3.3.1 Easy Query


SELECT
xmlinfo.query('/stuInfo[age>17]') as Result ,
xmlinfo.query('/stuInfo/name') as name ,
xmlinfo.value('(/stuInfo/age)[1]','nvarchar(3)') as age
From
[XQueryTable]
Reslut:



SELECT
xmlinfo.query('/stuInfo[age>18]')
as Result ,
xmlinfo.query('/stuInfo/name') as name ,
xmlinfo.value('(/stuInfo/age)[1]','nvarchar(3)') as age
From
[XQueryTable]
Reslut:


Notice: I write a
procedure to filter blank rows, the effect is:

Notice: if don&#8217;t
want to show the xml format strings ,just show the node value only,must use
value method. query like this:
SELECT
xmlinfo.value('(/stuInfo/name)[1]','nvarchar(max)') as name ,
xmlinfo.value('(/stuInfo/age)[1]','nvarchar(3)') as age,
xmlinfo.query('/stuInfo[age>18]')
as Result
From
[XQueryTable]




Other query:
SELECT xmlinfo.value('(/stuInfo/name)[1]','nvarchar(max)') as name from
XQueryTable
SELECT xmlinfo.query('/stuInfo[name="Jean"]') as NameisJean from XQueryTable
SELECT xmlinfo.exist('/stuInfo/name') as result from dbo.XQueryTable



3.3.2  Statement
With namespace



Namespace


WITH XMLNAMESPACES( 'http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-10-21T21:12:27'
AS "my")

SELECT
xmlinfo.query('/my:meetingAgenda/my:meeting[my:meetingOrganizer="AXA"]
') as
Result
FROM dbo.XQueryTable



For
　　The for clause in a FLWOR expression enables users to define a declarative
iteration of a bound variable over an input sequence. The input sequence can be
specified using XPath expressions, sequence of atomic values, a sequence
constructed using literals, or constructor functions. It is therefore analogous
to the SQL SELECT FROM clause and is not like a programming language
"for" construct.
　　Variable binding is also specified in the for clause.
Example: Selecting all home address
elements from resume using the for clause
　　The following query selects all Address nodes where the type of
address is set to Home and the JobCandidateID is 3:
SELECT Resume.query('
   declare namespace
RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
   for $A in /RES:Resume/RES:Address/RES:Addr.Type[.="Home"]/..
   return
   $A
') as Result
FROM
[HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

WITH XMLNAMESPACES( 'http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-10-21T21:12:27'
AS "my")

SELECT
xmlinfo.query(' for $A in
/my:meetingAgenda/my:meeting[my:meetingOrganizer="AXA"]/.. return $A
') as
Result
FROM dbo.XQueryTable



where
　　The where clause filters the results of an iteration by applying the
expression specified with the where clause.
Example: Selecting all home address
elements using the where clause
　　The following query selects all Address nodes where the type of
address is set to Home and the JobCandidateID is 3:
SELECT Resume.query('
   declare namespace
RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
   for $A in
/RES:Resume/RES:Address
   where
$A/RES:Addr.Type[.="Home"]
   return
   $A
') as Result
FROM
[HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

WITH XMLNAMESPACES( 'http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-10-21T21:12:27'
AS "my")

SELECT
xmlinfo.query('
for $A in
/my:meetingAgenda/my:meeting
   where
$A/my:meetingOrganizer[.="AXA"]
   return
   $A
')

as Result FROM dbo.XQueryTable



order by
　　The order by keyword enables you to sort the values in the returned result set.
The order by
keyword accepts a sorting expression, which should return an atomic value.
Optionally, you can also specify ascending or descending
for the sort order. The default sort order is ascending.
Example: Selecting employment history
in ascending order using the order by clause
　　The following query selects all Employment nodes in ascending order
of employment starting date for a candidate whose JobCandidateID is 3:
SELECT Resume.query('
   declare namespace
RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
   for $EMP in
/RES:Resume/RES:Employment
   order by
$EMP/RES:Emp.StartDate
   return
   $EMP
') as Result
FROM
[HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

return
　　The return clause, which is analogous to the SELECT clause in SQL, enables
users to define the result of a query. You can specify any valid XQuery
expression in the return clause. You can also construct XML structures in the return section by specifying
constructors for elements, attributes, etc.
Example: Selecting specific elements of
employment history using the return clause
　　The following query selects StartDate, EndDate, OrgName,
JobTitle elements of Employment node for a candidate whose JobCandidateID is 3:
SELECT Resume.query('
   declare namespace
RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
   for $EMP in
/RES:Resume/RES:Employment
   order by
$EMP/RES:Emp.StartDate
   return
   
<Employment>
     
{ $EMP/RES:Emp.StartDate }
     
{ $EMP/RES:Emp.EndDate }
     
{ $EMP/RES:Emp.OrgName }
     
{ $EMP/RES:Emp.JobTitle }
   
</Employment>
') as Result
FROM
[HumanResources].[JobCandidate]
WHERE JobCandidateID = 3


3.3.3 Composite condition


WITH XMLNAMESPACES(
      
'http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-10-21T21:12:27'
AS
      
"my")
SELECT
xmlinfo.query(' for $A in
/my:meetingAgenda/my:meeting
where
   
($A/my:meetingOrganizer[.="AXA"] or
$A/my:meetingOrganizer[.="IBM"])
    and
   
($A/my:meetingLocation[.="001"] )
    return
$A/.')

as Result
FROM
dbo.XQueryTable

3.3.4 Value comparison operators


　　Value comparison operators help
compare atomic values. Value comparison operators supported by SQL Server are eq, ne, lt, gt, le, and ge
　　The following query selects all Education nodes where the GPA is greater than 3.5 for the candidate whose JobCandidateID is 2:
SELECT Resume.query('
   declare namespace
RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
   for $ED in
/RES:Resume/RES:Education
   where
xs:decimal($ED/RES:Edu.GPA) gt 3.5
   return
   $ED
') as
Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2







3.3.5 The if-then-else Construct


SELECT Resume.query('
   declare namespace
RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
   for $A in
/RES:Resume/RES:Address
   return
      if (
$A/RES:Addr.Type eq "Home" )
      then
        
<Result>Home Address</Result>
      else
        
<Result>Other Address</Result>
') as
Result
FROM [HumanResources].[JobCandidate]
WHERE
JobCandidateID = 3

WITH XMLNAMESPACES( 'http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-10-21T21:12:27'
AS "my")

SELECT
xmlinfo.query('
if (
/my:meetingAgenda/my:meeting/my:meetingOrganizer[.="AXA"] )
then
      <abc>MeetingOrganizer
AXA</abc>
else
      /my:meetingAgenda/my:meeting/my:meetingOrganizer

      ') as Result,ProductName

FROM dbo.XQueryTable




3.4  Built-in XQuery Functions



Data Accessors
　　You can use the data accessor functions to extract values
of nodes as strings or typed values. XQuery supports two types of data accessor
functions: string(), which extracts the string value of an item and data(), which gets the typed value.
If the node is not a text node, an attribute node, or an element node then the data() function throws a static
error. If the node is a document node of an untyped XML instance, then data() returns a string value of
the document. The data() function returns a static error if the node is a complex typed
element.
Example: Using the data() function
　　The following query generates the employment history of a
candidate by using the data() function and computed element constructors:
SELECT Resume.query('
   declare namespace
RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
   for $ED in
/RES:Resume/RES:Education
   where xs:decimal(
data($ED/RES:Edu.GPA) ) gt 3.5
   return
     element Education
     {
      element
Level { data($ED/RES:Edu.Level) },
      element
Degree { data($ED/RES:Edu.Degree) },
      element GPA
{ data($ED/RES:Edu.GPA) },
      element
GPAScale { data($ED/RES:Edu.GPAScale) }
     }
') as
Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2



String Manipulation
　　XQuery supports four string manipulation functions:

• concat() Helps concatenate two or more strings.
  
• contains() Helps determine whether or not a string specified as the first
  operand contains another string specified as the second operand. The
  length of the search string is limited to 4,000 Unicode characters.
  
• substring() Helps extract portion of a string from another string known as
  source string.
  
• string-length() Helps calculate the length of a string.
  

Aggregate Functions
　　Aggregate functions operate on a sequence of items and
return the aggregate values of the sequence. Aggregate functions currently
supported in the XQuery support in SQL Server 2005 are count(), min(), max(), avg(), and sum(). The functions min() and max() accept only base types that
support the gt
operator (i.e., the three built-in numeric base types, the date/time base
types, xs:string, xs:boolean, and xdt:untypedAtomic). A sequence of mixed types
is not supported in these functions. Furthermore, xdt:untypedAtomic is treated
as xs:double.
　　For avg() and sum(), the type of the passed expression needs to be a subtype of one of
the three built-in numeric base types or untypedAtomic (but not a mixture,
xdt:untypedAtomic is treated as xs:double).
　　The count() function returns the number of items in a sequence.
Example: Using the avg() function
　　The following query calculates weekly average high and low
temperatures for the cities of New York and Boston using the avg() function:
DECLARE @Weather xml
SET @Weather = '
<WeatherInfo>
   <NewYork>
      <Temp
Date="2004-11-01" High="55" Low="45" />
      <Temp
Date="2004-11-02" High="58" Low="42" />
      <Temp
Date="2004-11-03" High="60" Low="40" />
      <Temp
Date="2004-11-04" High="51" Low="47" />
      <Temp
Date="2004-11-05" High="54" Low="41" />
      <Temp
Date="2004-11-06" High="55" Low="43" />
      <Temp
Date="2004-11-07" High="58" Low="47" />
   </NewYork>
   <Boston>
      <Temp
Date="2004-11-01" High="53" Low="45" />
      <Temp
Date="2004-11-02" High="56" Low="42" />
      <Temp
Date="2004-11-03" High="54" Low="41" />
      <Temp
Date="2004-11-04" High="52" Low="45" />
      <Temp
Date="2004-11-05" High="52" Low="36" />
      <Temp
Date="2004-11-06" High="54" Low="41" />
      <Temp
Date="2004-11-07" High="56" Low="44" />
   </Boston>
</WeatherInfo>'

SELECT @Weather.query('
   <WeatherInfo>
     
<NewYork>
        
<AvgHigh>{ avg(/WeatherInfo/NewYork/Temp/@High) }</AvgHigh>
        
<AvgLow>{ avg(/WeatherInfo/NewYork/Temp/@Low) }</AvgLow>
     
</NewYork>
     
<Boston>
        
<AvgHigh>{ avg(/WeatherInfo/Boston/Temp/@High) }</AvgHigh>
        
<AvgLow>{ avg(/WeatherInfo/Boston/Temp/@Low) }</AvgLow>
     
</Boston>
   </WeatherInfo>
') as
Result




Context Functions
　　You can use the context functions to obtain the contextual
properties of a context item. SQL Server 2005 implements two context functions&#8212;last() and position(). The last() function can be used to
determine the number of items in a sequence and the position() function can be used to
obtain the position of a context item. Both the last() and position() functions without an
argument can only be used in the context of a context-dependent predicate
(i.e., inside []) in SQL Server 2005.

3.5 Type-Related Expressions
　　XQuery supports various types of expressions or operators
that are based on the type information. These expressions can be classified as
type assertion expressions, type inspection expressions, and type casting
expressions. These expressions are discussed briefly in the following sections.

3.5.1 Type Assertion Expressions
　　You can use the as clause to specify the
type for the binding variable used in the for statement.
　　When a type is declared for the binding variable, binding
values that are not of the declared type would result in type error. The
xs:TYPE clause is not a cast expression but it serves as a type assertion.
Example: Using "as xs:TYPE"
clause with for statement
　　The following query binds the address node sequence to a
variable $A which is defined as type element(RES:Address):
SELECT Resume.query('
   declare namespace
RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
   for $A as
element(RES:Address) in /RES:Resume/RES:Address
   return   
      $A
') as
Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2



3.5.2 Type Casting Expressions

Implicit Type Casting
　　The XQuery engine performs implicit type casting for
numeric types and untypedAtomic values in expressions that contain arithmetic
operations or function invocations. This process is known as type promotion.
Type promotion occurs when an expression results in a numeric type that is
incompatible with the expected numeric type. Type promotion is performed by
casting the resulting expression to the required type.
Example: Implicit type casting
　　The following query performs an arithmetic operation on a
decimal value and a double value. In the current scenario, the values in the
expression are added only after promoting the xs:decimal value to xs:double.
DECLARE @Result xml
SET @Result = '<Result />'
SELECT @Result.query('
   <Result>{
xs:decimal("10.55") + xs:double(1.5e1) }</Result>
') as
Result

Explicit Type Casting

3.5.3 Typed value constructors
　　XQuery provides constructor functions for all built-in
types defined in the XML Schema specification. These constructors are useful
for constructing typed values and also for casting values from one type to
another. XQuery also makes constructors available for types that are defined in
imported schemas.
Example: Using a value constructor for
constructing values
　　The following query returns all Employment
nodes for which the StartDate is greater than a value
constructed using constructor for type xs:date:
SELECT Resume.query('
   declare namespace
RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
   for $EMP in
/RES:Resume/RES:Employment
   where $EMP/RES:Emp.StartDate
gt xs:date("1995-01-01")
   return   
      $EMP
') as
Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3
Example: Using a value constructor for
typecasting
　　The following query selects all Education
nodes where the GPA is greater than or equal to 3.8 for the
candidate whose JobCandidateID is 2. This query uses the value
constructor for xs:decimal for typecasting the value of Edu.GPA from xs:string
to xs:decimal:
SELECT Resume.query('
   declare namespace
RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
   for $ED in
/RES:Resume/RES:Education
   where xs:decimal(
data($ED/RES:Edu.GPA) ) ge 3.8
   return
     element Education
     {
      element
Level { string($ED/RES:Edu.Level)},
      element
StartDate { string($ED/RES:Edu.StartDate)},
      element
EndDate { string($ED/RES:Edu.EndDate)},
      element
Degree { string($ED/RES:Edu.Degree)},
      element GPA
{ string($ED/RES:Edu.GPA)},
      element
GPAScale { string($ED/RES:Edu.GPAScale)}
     }
') as
Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2

3.5.4 cast as xs:TYPE ? Operator
　　XQuery in SQL Server 2005 supports the cast as TYPE
? operator, which is useful for performing explicit type casting.
Explicit type casting can also be performed using the xs:TYPE()
constructors, which are more convenient to write than the cast as TYPE
? operator.
Example: Using "cast as xs:TYPE
?" operator
　　The following query generates an XML that contains typed
values of selected elements from Education node set for a
candidate whose JobCandidateID is 3:
SELECT Resume.query('
   declare namespace
RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
   for $ED in
/RES:Resume/RES:Education
   return
     element Education
     {
      element
Level { $ED/RES:Edu.Level cast as xs:string? },
      element
StartDate { $ED/RES:Edu.StartDate cast as xs:date? },
      element
EndDate { $ED/RES:Edu.EndDate cast as xs:date? },
      element
Degree { $ED/RES:Edu.Degree cast as xs:string? },
      element GPA
{ $ED/RES:Edu.GPA cast as xs:decimal? },
      element
GPAScale { $ED/RES:Edu.GPAScale cast as xs:decimal? }
     }
') as
Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2
Example: Using "xs:TYPE()"
operator
　　The following query generates the same results as the query
in the previous example using the xs:TYPE() operator instead
of the cast as xs:TYPE ? operator:
SELECT Resume.query('
   declare namespace
RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
   for $ED in
/RES:Resume/RES:Education
   return
     element Education
     {
      element
Level { xs:string($ED/RES:Edu.Level) },
      element
StartDate { xs:date($ED/RES:Edu.StartDate) },
      element
EndDate { xs:date($ED/RES:Edu.EndDate) },
      element
Degree { xs:string($ED/RES:Edu.Degree) },
      element GPA
{ xs:decimal($ED/RES:Edu.GPA) },
      element
GPAScale { xs:decimal($ED/RES:Edu.GPAScale) }
     }
') as
Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2