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:

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:

·         Default.aspx : start page;

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

Report Design View

 

 

 

 

 


 

 

 

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:

 

 

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’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—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

 

Last edited Jun 10, 2011 at 2:11 PM by starcrm, version 2

Comments

No comments yet.