Approve/Reject Multiple Items of List in SharePoint 2010

You can approve/Reject an item from SharePoint ribbon. But only one item can be approved or rejected. But I’ve found requirements from few of my clients that they want to approve/reject in batch rather than one by one. This is logical. If there’s 100 of items to approve/reject, doing this one by one is tedious. In this post I’ve described how I’ve implemented the idea and at the end of the blog you can find the link to download the source code.

My approach to allow multiple approve/reject in batch is following the steps:

  • Add a new ribbon “Approve/Reject Selection” as shown below. The new ribbon will be active when more than one item will be selected.

Approve/Reject Multiple Items of List in SharePoint 2010 

Figure 1: New ribbon “Approve/Reject Selection” added

  • When multiple item will be selected from grid the “Approve/Reject Selection” will be active as shown below:
  • Approve/Reject Multiple Items of List in SharePoint 2010

    Figure 2: “Approve/Reject Selection” will be active when multiple items will be selected

    Clicking on “Approve/Reject Selection” will bring up a new custom window developed my me as shown below:

    Approve/Reject Multiple Items of List in SharePoint 2010 

    Figure 3: Approve/Reject Multiple items dialog

    • Finally, the custom dialog shown in figure 3, is an application page where we need to write code to approve/reject selected items programmatically.

    So let’s start with the process of implementing the idea!!!!!!!!!!!

    Step 1: Create a custom ribbon

    First add a new empty element as shown below:

    Approve/Reject Multiple Items of List in SharePoint 2010

    Figure 4: Add new empty element

    Then add the following xml in the elements.xml file:

    <?xml version="1.0" encoding="utf-8"?>
    <Elements xmlns="http://schemas.microsoft.com/sharepoint/">
    <CustomAction Id="COB.SharePoint.Ribbon.NewControlInExistingGroup" Location="CommandUI.Ribbon.ListView" RegistrationType="List" RegistrationId="100">
    <CommandUIExtension>
    <CommandUIDefinitions>
    <CommandUIDefinition Location="Ribbon.ListItem.Workflow.Controls._children">
    <Button Id="COB.SharePoint.Ribbon.NewControlInExistingGroup.Notify" Command="COB.Command.NewControlInExistingGroup.Notify" Sequence="21" Image16by16="/_layouts/$Resources:core,Language;/images/formatmap16x16.png" Image16by16Top="-48" Image16by16Left="-240" Image32by32="/_layouts/$Resources:core,Language;/images/formatmap32x32.png" Image32by32Top="-448" Image32by32Left="-384" Description="Uses the notification area to display a message." LabelText="Approve/Reject Selection" TemplateAlias="o1"/>
    </CommandUIDefinition>
    </CommandUIDefinitions>
    <CommandUIHandlers>
    <CommandUIHandler Command="COB.Command.NewControlInExistingGroup.Notify" EnabledScript="javascript:enableApprovalAll();" CommandAction="javascript: showApproveAll(); "/>
    </CommandUIHandlers>
    </CommandUIExtension>
    </CustomAction>
    <CustomAction Id="COB.Command.NewControlInExistingGroup.Notify.Script" Location="ScriptLink" ScriptSrc ="/_layouts/SharePoint.ApproveRejectTest/Scripts/ApproveReject.js"/>
    </Elements>







    Figure 5: Code snippet for Custom Ribbon



    I’m not going to describe the code snippet at figure 5 elaborately. However the basic things are that, I’m adding a button with label “Approve/Reject Selection” and I’ve associated two commands with the button. One is when to enable/disable the button with CommandUIHandler’s EnableScript attribute. The buttton click event action is defined with CommandAction attribute. If you notice I’ve just mentioned two javascript function enableApproveAll and showApproveAll. These two functions are not defined in this xml. Rather they are defined in another file “/_layouts/SharePoint.ApproveRejectTest/Scripts/ApproveReject.js” which is referenced in xml file.



    Step 2: Create the script file to show/hide approve/reject dialog


    The content of the ApproveReject.js file is show below. The command to show the approve/reject dialog is declared in this script. The function showApproveAll() will show a custom application page that I’ve described in step 3. The reference section in the file helps to get intellisense. I’ve not explained the script that much as it’s not in the scope of this post.




    function showApproveAll() 
    {
    var ctx = SP.ClientContext.get_current();
    var ItemIds = "";
    //get current list id
    var listId = SP.ListOperation.Selection.getSelectedList();
    //get all selected list items
    var selectedItems = SP.ListOperation.Selection.getSelectedItems(ctx);
    //collect selected item ids
    for (var i = 0; i < selectedItems.length; i++)
    {
    ItemIds += selectedItems[i].id + ",";
    }
    //prepare cutom approval page with listid
    //and selected item ids passed in querystring
    var pageUrl = SP.Utilities.Utility.getLayoutsPageUrl('/SharePoint.ApproveRejectTest/ApproveAll.aspx?ids=' + ItemIds + '&listid=' + listId);
    var options = SP.UI.$create_DialogOptions();
    options.width = 420;
    options.height = 250;
    options.url = pageUrl;
    options.dialogReturnValueCallback = Function.createDelegate(null, OnDialogClose);
    SP.UI.ModalDialog.showModalDialog(options);
    }
    //used to determine whether the 'approve/reject selection'
    //ribbon will be enalbed or disabled
    function enableApprovalAll()
    {
    var ctx = SP.ClientContext.get_current();
    return SP.ListOperation.Selection.getSelectedItems(ctx).length > 1;
    }
    //called on dialog closed
    function OnDialogClose(result, target)
    {
    //if ok button is clicked in dialog, reload the grid.
    if (result == SP.UI.DialogResult.OK)
    {
    location.reload(true);
    }
    }







    Figure 6: ApproveReject.js file





    In the method showApproveAll, I’ve collected the selected Items’ IDs and passed to “ApproveAll.aspx” page as querystring. I’ve also passed the current list id in querystring.



    Step 3: Create custom Approve/Reject application page


    Finally I’ve developed a application page named as “ApproveAll.aspx”. The partial markup of the page is shown below:




    <asp:Content ID="Main" ContentPlaceHolderID="PlaceHolderMain" runat="server">
    <script type="text/javascript">
       1:  
       2:         function closeDialog() 
       3:         {
       4:             SP.UI.ModalDialog.commonModalDialogClose(SP.UI.DialogResult.cancel, 'Cancelled clicked');
       5:         }
       6:         function finisheDialog() 
       7:         {
       8:              SP.UI.ModalDialog.commonModalDialogClose(SP.UI.DialogResult.OK, 'Cancelled clicked');
       9:          }
      10:     
    </script>
    <h2 id="divMessage" runat="server"> </h2>
    <table>
    <tr>
    <td> Status: </td>
    <td>
    <asp:DropDownList ID="ddlAprovalOptions" runat="server">
    <asp:ListItem Text="Approve" Value="Approved" />
    <asp:ListItem Text="Pending" Value="Pending" />
    <asp:ListItem Text="Reject" Value="Denied" />
    </asp:DropDownList>
    </td>
    </tr>
    <tr>
    <td> Comments: </td>
    <td>
    <asp:TextBox ID="txtComments" runat="server" TextMode="MultiLine" Columns="40" Rows="5" MaxLength="255" />
    </td>
    </tr>
    <tr>
    <td> </td>
    <td>
    <asp:Button ID="btnSubmit" runat="server" Text="OK" OnClick="btnOk_Click" />
    <input type="button" runat="server" id="btnCancel" value="Cancel" onclick="closeDialog()" />
    </td>
    </tr>
    </table>
    </asp:Content>





    Figure 7: ApproveAll.aspx page’s markup





    In the code behind of the page, you need to extract the list id and list item ids. Then you need to invoke a method like shown below to approve/reject items:



     




    private void ApproveRejectItems(SPWeb web, string listId, SPModerationStatusType moderationStatusType, List<int> itemIDs)
    {
    web.AllowUnsafeUpdates = true;
    SPList spList = web.Lists[new Guid(listId)];
    foreach (var itemId in itemIDs)
    {
    SPListItem spListItem = spList.GetItemById(itemId);
    //disable workflow
    foreach (SPWorkflow workflow in spListItem.Workflows)
    {
    if (workflow.ParentAssociation.Id == spList.DefaultContentApprovalWorkflowId)
    {
    SPWorkflowManager.CancelWorkflow(workflow);
    }
    }
    //update moderation status
    spListItem.ModerationInformation.Comment = txtComments.Text;
    spListItem.ModerationInformation.Status = moderationStatusType;
    spListItem.Update();
    }
    }





    Figure 8: Approve/Reject items



    As shown in the code snippet in figure 8, first we need to make sure we disable content approval workflow, if exists. Then we can update the moderation status.



    The full source code can be download from My Skydrive.

    Custom add/edit/display form for list in SharePoint

    Sometimes we don’t want to use SharePoint’s custom add/edit/display form. We want our own custom form when user will try to view, edit or add an item. I’ll show you today how easily we can do so.

    For this post I’ll consider I’ll have a list with three fields: Product Code, ProductName and ProductDescription. I’ll show how we can create a list with these two fields with custom add/edit/display form. The list’s fields are described in the table below:

    Custom add/edit/display form for list in SharePoint Table 1: Custom list template/Content Type’s fields

    The first step of this approach is to create a content type with required fields associated with the content type. The noticeable point here is that In the content type declaration, we can define custom forms for add/edit/display.

    Step 1: Create a content type for your list

    To create content type right click on your project and click ‘add new item’ and then select content type as shown below:

    Custom add/edit/display form for list in SharePoint

    Figure 1: Add content type

    Next you will be prompted for the base content type as shown below: If you want to create a custom list, you can select Item as shown below:

    Custom add/edit/display form for list in SharePoint

    Figure 2: ‘Item’ is the base content type for custom/generic list

    Then you will have provided the content xml file. You need to modify the content type xml file as shown below. Please modify the Inherits=”False” from the content types.

    <?xml version="1.0" encoding="utf-8"?>
    <Elements xmlns="http://schemas.microsoft.com/sharepoint/">
    <!--Defined fields-->
    <Field ID="{30C3D21A-A7C9-410E-A896-82875475F697}" Name="ProductName" DisplayName="Product Name" Type="Text" Required="FALSE" > </Field>
    <Field ID="{9621763e-3494-4a86-a3eb-fd2593f1a1f1}" Name="ProductDescription" DisplayName="Product Description" Type="Text" > </Field>
    <!-- Parent ContentType: Item (0x01) -->
    <ContentType ID="0x0100c5e54b7f62ad451a92f9235d43ec9082" Name="ProductContentType" Group="Custom Content Types" Description="My Content Type" Inherits="false" Version="0">
    <FieldRefs>
    <FieldRef ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" Name="Title" DisplayName="Product Code" Sealed="TRUE"/>
    <FieldRef ID="{82642ec8-ef9b-478f-acf9-31f7d45fbc31}" Name="LinkTitle" DisplayName="Product Code" Sealed="TRUE"/>
    <FieldRef ID="{BC91A437-52E7-49E1-8C4E-4698904B2B6D}" Name="LinkTitleNoMenu" DisplayName="Product Code" Sealed="TRUE" />
    <FieldRef ID="{30C3D21A-A7C9-410E-A896-82875475F697}" Name="ProductName" DisplayName="Product Name" Required="False" />
    <FieldRef ID="{9621763e-3494-4a86-a3eb-fd2593f1a1f1}" Name="ProductDescription" DisplayName="Product Description" Required="False" />
    </FieldRefs>
    <XmlDocuments>
    <XmlDocument NamespaceURI="http://schemas.microsoft.com/sharepoint/v3/contenttype/forms/url">
    <FormUrls xmlns="http://schemas.microsoft.com/sharepoint/v3/contenttype/forms/url">
    <Display>_layouts/blogtest/Product.aspx?mode=display</Display>
    <Edit>_layouts/blogtest/Product.aspx?mode=edit</Edit>
    <New >_layouts/blogtest/Product.aspx?mode=new</New>
    </FormUrls>
    </XmlDocument>
    </XmlDocuments>
    </ContentType>
    </Elements>







    Figure 3: Content Type xml file with fields and add/edit/display form declared



    Now let’s explain what’s in the xml shown in figure 3.




    • Firstly I’ve modified Inherits to false in ConentType tag.


    • I’ve defined two fields inside the <Elements> tag that I’ve used later in content types


    • Then used those fields in <FieldRefs> of <ContentType> tags. These fields will be available in Content type. I’ve also used three existing fields (for Title) from base Content Type (Item).


    • Finally I’ve defined New, Edit and Display form for these content types in <XmlDocuments> section.



    Step 2: Create a list Template based on Content type


    Now you have defined content types with three fields. Next step is to define a list template based on the content type. To do so click add new item from visual studio context menu and select “List Definition From Content Type” as shown below:



    Custom add/edit/display form for list in SharePoint



    Figure 4: Create list definition from content type in ‘Create new Item’ dialog.





    Next you will be prompted for available content types in the project as shown below. Remember to uncheck the button ‘Add a list instance for this list definition’ for this demo now.



    Custom add/edit/display form for list in SharePoint



    Figure 5: Create list definition from Content Type





    Now you will find two files Elements.xml and Schema.xml files are added. Our full focus will be now on Schema.xml.



    Modify the content in <Fields> tag:



    Ensure Title fields with display name ‘product code’ exists as shown below:




    <Field ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" Name="Title" DisplayName="Product Code" Sealed="TRUE" Type="Text" />





    Custom add/edit/display form for list in SharePoint



    Figure 6: Add title field in the list template (if not exists)





    Then find two fields LinkTitle and LinkTitleNoMenu. Then change their display name to ‘Product Code’ as shown below. These two fields are link to edit menu.



    Custom add/edit/display form for list in SharePoint



    Figure 7: Rename the displayName for linkTitle and LinkTitleNoMenu field





    Modify the content in <Views> tag



    Open the views tag and add the fields you want to display in default view under <View> with Default value is true as shown below.



    Custom add/edit/display form for list in SharePoint



    Figure 8: Define the fields to be shown in default view







    Step 3: Create Custom add/edit/display form


    Next step is to develop a custom application page to use for add/edit/display. As sown in figure 3, you can three different pages for add, edit and view. However for brevity I want to use a single page for all these three operations. You need to create an application page in appropriate location (in my case this is _layouts/blogtest folder). Rather than using three different files for add/edit/display, you can use a single page for all these three tasks as shown below:




    <XmlDocuments>
    <XmlDocument NamespaceURI="http://schemas.microsoft.com/sharepoint/v3/contenttype/forms/url">
    <FormUrls xmlns="http://schemas.microsoft.com/sharepoint/v3/contenttype/forms/url">
    <Display>_layouts/blogtest/Product.aspx?mode=display</Display>
    <Edit>_layouts/blogtest/Product.aspx?mode=edit</Edit>
    <New >_layouts/blogtest/Product.aspx?mode=new</New>
    </FormUrls>
    </XmlDocument>
    </XmlDocuments>







    By passing different parameter to a single page we can identity the page’s mode (add, edit or view). Also SharePoint by default add the list and item id at the end of the page. So your page’s url will look like for new item:



    _layouts/blogtest/Product.aspx?mode=new&List=LISTGUID&ID=ITEMID



    So from the page (Product.aspx) you can identity the list id and item id from querystring.



    I’m not showing details of the product.aspx page here.



    You can download the full source code from this skydrive link.



    How to use the project attached with this post?



    1. Download the code from here.


    2. Deploy the solution to a SharePoint site.


    3. Create a new list with “ProductListDefinition” template. This template will be installed in the site as you deploy the SharePoint solution.


    4. Now try to add/edit/view items in the list. You will find the custom form product.aspx is used for add/edit/view.

    Disable Event Receiver From non-receiver code in SharePoint

    When we are in List Item Event Receiver code, we can modify/update the same item which will fire the event receiver again. For disabling event receiver to get fired again we can use the property ‘’ as shown below:

    public class TestEventReceiver:SPItemEventReceiver
    {
    public override void ItemAdded(SPItemEventProperties properties)
    {
    //disable event receiver firing
    EventFiringEnabled = false;
    //do something
    //enalbe event receiver firing
    EventFiringEnabled = true;
    }
    }







    Figure 1: Sample code to enable/disable event receiver firing inside event receiver handler.



    However, if you are in a webpart and want to modify an item but don’t want to fire event receiver, then? Don’t worry there’s a way out. I’ll explain this today.





    What happens when Event Receiver disabled?


    When you disable event receiver, SharePoint internally set a data field in current Thread. So if you can set your current’s thread’s data to the required value before/after updating item, you can control the event receiver. However, setting current thread data manually might be risky and I’ll not use that path. Rather I’ll show how we can use existing power of ‘SPEventReceiverBase’ to control event receiver firing.



    Create your own Event Receiver Controller


    I’ve create a custom class inherited from ‘SPEventReceiverBase’. From this base class I get a properties ‘EventFiringEnabled’ which allows me to control the event receiver firing. The following code snippet shows my custom EventReceiverManager:




    public class EventReceiverManager : SPEventReceiverBase, IDisposable
    {
    public EventReceiverManager(bool disableImmediately)
    {
    EventFiringEnabled = !disableImmediately;
    }
    public void StopEventReceiver()
    {
    EventFiringEnabled = false;
    }
    public void StartEventReceiver()
    {
    EventFiringEnabled = true;
    }
    public void Dispose()
    {
    EventFiringEnabled = true;
    }
    }







    Figure 2: A EventReceiverManager custom class to control event receiver firing.



    The code snippet above is derived from SharePoint’s SPEventReceiverBase to use the ‘EventFireingEnabled’ property.



    Now you can use this EventReceiverManager to control the event receiver. To stop firing the event receiver on any changes, you need to wrap the code block inside EventReceiverManager as shown below:




    using (var eventReceiverManager = new EventReceiverManager(true))
    {
    var list = GetList("listName");
    var listItem = list.GetItemById(itemId);
    listItem["field"] = "value";
    listItem.Update();
    }







    Figure 3: How to use EventReceiverManager to disable event receiver firing.



    As shown above even if he list has event receiver for ItemUpdated/ItemDating the event receiver will not get fired because of putting the code in EventReceiverManager block. Please notice of ‘using’ block, as soon as you leave the ‘using’ block, the event firing in enabled automatically. This is because in Dispose method of EventReceiverManager I’ve enabled the event firing.

    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[i].Split(',')[0], arrColumnAssociate[i].Split(',')[1]);

    valueAssociate.Add(arrColumnAssociate[i].Split(',')[0], xDoc.SelectSingleNode(arrColumnAssociate[i].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:



    · ProductName



    · XmlInfo: XML data type field, to store SharePoint form library's XML file



    DEMO:


    Step1:design the demo infopath template:


    Introduction To SharePoint XQuery Reporting Solution



    Step2:Pulish template and fill some dome data:



    Introduction To SharePoint XQuery Reporting Solution



    Step3:Save the data to form library:



    Introduction To SharePoint XQuery Reporting Solution



    Step4: Prepare the demo datebase,



    Introduction To SharePoint XQuery Reporting Solution



    Step5:Config the XMLtoDB project



    Introduction To SharePoint XQuery Reporting Solution



    Step6: Run the XMLtoDB.exe program



    Introduction To SharePoint XQuery Reporting Solution



    Step7:Check database. Xml has saved to database.



    Introduction To SharePoint XQuery Reporting Solution



    1.2 XQuery Web Demo



    Query the database XML type field.



    Major files:



    · Default.aspx : start page;



    · SearchElement.ascx : user control for auto generate query conditions;



    Demo:



    Step1:Try to query xml field


    Introduction To SharePoint XQuery Reporting Solution



    Step2:Try XQuery. It is work!



    Introduction To SharePoint XQuery Reporting Solution



    1.3 XQuery Report Project



    Step1:Install and config SQL Server Reporting Service


    Introduction To SharePoint XQuery Reporting Solution



    Introduction To SharePoint XQuery Reporting Solution



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


    Introduction To SharePoint XQuery Reporting Solution



    Create a new report item



    Introduction To SharePoint XQuery Reporting Solution



    Step3:Datasource config


    Introduction To SharePoint XQuery Reporting Solution



    If choose Database as datasource:



    Introduction To SharePoint XQuery Reporting Solution



    Note:



    l XQuery can’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.



    Introduction To SharePoint XQuery Reporting Solution



    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.



    Introduction To SharePoint XQuery Reporting Solution



    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.



    Introduction To SharePoint XQuery Reporting Solution



    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.



    Introduction To SharePoint XQuery Reporting Solution



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



    Introduction To SharePoint XQuery Reporting Solution



    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.



    Introduction To SharePoint XQuery Reporting Solution



    Step8:Add a ReportViewer control to the Web page



    Introduction To SharePoint XQuery Reporting Solution



    Step9:Run the application



    Introduction To SharePoint XQuery Reporting Solution



    Find some word:



    Introduction To SharePoint XQuery Reporting Solution



    Save to PDF



    Introduction To SharePoint XQuery Reporting Solution



    Step10:Design matrix report



    Introduction To SharePoint XQuery Reporting Solution



    Introduction To SharePoint XQuery Reporting Solution



    Introduction To SharePoint XQuery Reporting Solution



    Group:



    Introduction To SharePoint XQuery Reporting Solution



    Expression:



    Introduction To SharePoint XQuery Reporting Solution



    Child group:



    Introduction To SharePoint XQuery Reporting Solution



    Step11:Run



    Introduction To SharePoint XQuery Reporting Solution



    Chart report:



    Introduction To SharePoint XQuery Reporting Solution



    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.



    Introduction To SharePoint XQuery Reporting Solution



    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:



    · 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.



    · 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.



    · 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.



    · 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.



    · 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:



    Introduction To SharePoint XQuery Reporting Solution






    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:



    Introduction To SharePoint XQuery Reporting Solution



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



    Introduction To SharePoint XQuery Reporting Solution



    Notice: if don’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]





    Introduction To SharePoint XQuery Reporting Solution



    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





    Introduction To SharePoint XQuery Reporting Solution



    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





    Introduction To SharePoint XQuery Reporting Solution



    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







    image040

    Context Functions


    You can use the context functions to obtain the contextual properties of a context item. SQL Server 2005 implements two context functions—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