Jeremy Cottino

Il est bien plus beau de savoir quelque chose de tout que de savoir tout d'une chose. [Blaise Pascal]

Using XML within SQL Server 2008

1 comment
During my work for the SQL certification (70-433 TS: Microsoft SQL Server 2008, Database Development), I found many difficult questions (difficult for me…) around the usage of XML within SQL server.
This article will not deal with all XML features that are available within SQL Server 2008, but just give some key points that I need to remember for the exam:

The XML Schema repository
For more information on the usage of XML Schema and XSD file format, you can just Google it; I found for example this article on Wikipedia.
You can add as many XML schemas in SQL server as you may need. To add a new XML Schema, the TSQL command is:

CREATE XML SCHEMA COLLECTION MyXmlSchema N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema xmlns="..." ...>

Note also the availability of following queries:

The xml Native Data Type
SQL Server 2005 supports a new native data type named xml that you use in exactly the same way as any other built-in data type

Typed and Un-typed xml Columns
When you provide a schema for the XML documents you will store, you can create a typed xml column in a table:
CREATE TABLE MyTable(MyKey int, MyXml xml(MyXmlSchema))

Without providing schema, you create an un-typed xml column:
CREATE TABLE MyTable(MyKey int, MyXml xml)

Inserting and Selecting on an xml Column
You can insert an XML document into an xml column in the same way as you would do for any other built-in data type. You can insert it as a string value, or use the CAST or CONVERT functions to specifically convert it to an xml type:
INSERT INTO MyTable(MyKey, MyXml)
      VALUES (1, '<FootBallClub id="1"><Name>Olympique de Marseille</Name><Position>1</Position></FootBallClub>')
INSERT INTO MyTable(MyKey, MyXml)
      VALUES (2, CAST('<FootBallClub id="2"><Name>Nimes Olympique</Name><Position>2</Position></FootBallClub>' AS xml))
INSERT INTO MyTable(MyKey, MyXml)
      VALUES (3, CONVERT(xml, '<FootBallClub id="3"><Name>Milan AC</Name><Position>100</Position></FootBallClub>'))

To extract an XML document from an xml column, you can convert it to nvarchar data type for example:
SELECT CONVERT(nvarchar(500), MyXml) as MyXmlDoc FROM MyTable

Querying XML in SQL server

XQuery support is available since SQL Server 2005. If you wish to have a tutorial about XQuery, you can go on the W3C Web Site

The following methods are available for the xml object in SQL Server:
  • The query method, which returns a fragment of un-typed XML
  • The value method, which returns a single value from the XML and exposes it as a standard (non-xml) SQL data type
  • The exist method, which can be used to test whether a specific node/value exists in the XML data
  • The modify method, which executes an XML Data Modification Language (XML-DML) statement
  • The nodes function, which returns a single-column rowset of nodes from the XML

Using the query Method
These are example of SQL statements that query the table we just created:
SELECT MyXml.query('/FootBallClub[@id="1"]/Name') FROM MyTable
The result is:
<Name>Olympique de Marseille</Name>

To get just the value of the matching element, you can use the data function:
SELECT MyXml.query('data(/FootBallClub[@id="1"]/Name)') FROM MyTable  
In this case, the result would be just:
Olympique de Marseille

If the xml column is a typed column, you must specify the namespace:
SELECT MyXml.query('declare namespace s="http://blablabla"; ...

If you are familiar with FLWOR XQuery’s (For/Let/Where/Order by/Return), you can build an Xml document that contains all FootBallClub that have Id’s lower than 5.
Let’s add this document in a new row, of our table:
<FootBallClub id="1">
<Name>Olympique de Marseille</Name>
<FootBallClub id="2">
<Name>Nimes Olympique</Name>
<FootBallClub id="3">
<Name>Milan AC</Name>

Here is the query
SELECT MyXml.query('
  for $p in //FootBallClub
  where data($p/@id) < 2
  order by $p/name[1]
  return $p/Name
FROM MyTable WHERE MyKey = 4

In this case the result is
<myclubs><Name>Olympique de Marseille</Name></myclubs>

Using the value Method

SELECT MyXml.value('(/FootBallClub[@id="1"]/Name)[1]', 'nvarchar(30)') FROM MyTable

Return Olympique de Marseille

Using the exist Method

SELECT MyXml.exist('(/FootBallClub[@id="1"]/Name)[1]') FROM MyTable
Return 1 (if false return 0)

Using the modify Method
…To delete a node
UPDATE MyTable SET MyXml.modify('delete /FootBallClub[@id="3"]')
WHERE MyKey = 4

…To insert a new node
UPDATE MyTable SET MyXml.modify('insert <FootBallClub id="4"><Name>Milan AC V2</Name><Position>101</Position></FootBallClub> before (/FootBallClub[@id="2"])[1]')
FROM MyTable WHERE MyKey = 4

To specify the destination position in case of insert statement, you can choose between:
{as first | as last} into | before | after

…to update a node
UPDATE MyTable SET MyXml.modify('replace value of (/FootBallClub[@id="4"]/Name/text())[1] with "Milan AC new name"')
FROM MyTable WHERE MyKey = 4

Note the text(): this need to be specified because we are using un-typed xml column. For typed xml, the statement would be
'replace value of (/FootBallClub[@id="4"]/Name)[1] with xs:string("Milan AC new name ")')

Using the nodes Method

SELECT T.colName.query('.')
FROM MyTable
CROSS APPLY MyXml.nodes('/FootBallClub') as T(colName)
This query returns all clubs present in each of the nodes of each column. In our case it returns 6 lines (1 in line 1, 1 in line 2, 1 in line 3 and 3 in line 4)

Passing Parameters to an XQuery
The following 2 queries are identical:

SELECT MyXml.query('//Name') FROM MyTable
WHERE MyXml.value('(/FootBallClub/@id)[1]', 'int') = 1

Or by using sql:variable
SET @clubId = 1
SELECT MyXml.query('//Name') FROM MyTable
WHERE MyXml.exist('/FootBallClub[./@id = sql:variable("@clubId")]') = 1

They return 2 lines that contains Olympique de Marseille (1 in line 1 and 1 in line 4)

In this last case we will use sql:column("MyKey") to query the table
SELECT MyXml.query('
  <MyColumnId>{ sql:column("MyKey") }</MyColumnId>
  <ClubName>{ data(//Name) }</ClubName>
  <ClubPosition>{ data(//Position) }</ClubPosition>
FROM MyTable WHERE MyKey = 1

1 comment :

Post a Comment

Note: Only a member of this blog may post a comment.

New free webinars

No comments
From PM Center Usa, 2 news and FREE interesting webinars:

  • Risk Management Made Easy for Projects on Oct. 28, 2010 18:00(CET), Register here
  • Earned Value Analysis – Why it Doesn’t Work on Nov. 4, 2010 17:00 (CET), Register here
0.5 PDU for each.
I would love to know the arguments of the speaker regarding Earned Value Management and the fact that this do not work... Will see...

No comments :

Post a Comment

Note: Only a member of this blog may post a comment.

Project Management: Understanding Earned Value Management

One of the the favorite question of a project sponsor during a steering meeting is “So, what is the status of my project?” If things are looking good, a frequent answer from the project manager often sounds like this: “Well, we seem to be within the budget and it looks like we will meet the target go live date…”. If things are a bit dicey, he or she might say “Well, there have been some roadblocks and bottlenecks, so it looks like the timeline might slip by a few weeks and we will also need an additional budget…” Now if I were a project sponsor, this is definitively not what I would want to hear. I will share in this post a good way to report on project status, using a simple methodology: the Earned Value Management.

The goal of Earned Value Management (EVM) is to objectively understand what was accomplished, and compare it to work planned, in other words what was spent and how. By using EVM, the project manager wants to manage cost (and schedule) rather than just monitoring and reporting it.

Let’s take an example of a project status. The project, 10000 CHF budget, 10 days effort, will produce 10 deliverables. The status report gives the following elements: Time elapsed 5 days, spent to date 6000 CHF, deliverables produced: 5 complete and 1 half done. What is the status of this project? How far along are we? What about the performance of the team? If we spend 60% of the budget, does that imply that we have 60% of the project completed?

Without EVM, we just make inspection. With EVM, we make management. EVM can help the team to accurately report on project status and accurately identify trends (and problems). The project manager is then able to answer following questions:
  • What is the status of the project?
  • What are the problems and how to fix them?
  • What is the impact of each problem, and what are present and feature risks?
Here are the basic elements used in EVM (note that money is used to monitor costs AND schedule)

Let’s take again our previous example to illustrate it. We need to produce 10 deliverables within 10000 CHF budget (=> 1000 CHF per deliverable), 5 days of work we already spent 6000 CHF and have 5 deliverables + 1 half done.

In the following graph, we just plot all information.

In most projects, the Planned Value looks like an S-Curve.
What is the status of this project? To know it we will use variance calculation:
Cost Variance CV= EV-AC = 5500-6000 = - 500 CHF negative => bad
Schedule Variance SV= EV-PV = 5500-5000 = + 500 CHF positive => good
Variances can also be represented as indexes. Those indexes can be viewed as the factor of performance.
Cost Performance Index CPI = EV / AC = 5500/6000 = 0.916 Less than 1 => bad
Schedule Performance Index SPI = EV / PV = 5500/5000 = 1.1 More than 1 => good
The Estimate At Completion EAC can be calculated in four different ways depending on the trends of the project:
  • If there is no variances in the project EAC ‘no variances’ = BAC / CPI
  • If the initial estimates are flawed EAC ‘fundamentally flawed’ = AC + ETC
  • If the trend will change EAC ‘atypical’ = AC + BAC – EV
  • If the trend will stay the same until the end EAC ‘typical’ = AC + ((BAC – EV) / CPI)
Let’s say for our example that the first estimate is flawed, we need to calculate the Estimate To Complete, ETC. 4.5 deliverables need to be finished.
EAC = AC + ETC = 6000+4500 = 10500 CHF
The Variance At Completion VAC can be now calculated as VAC = BAC – EAC = 10000-105000 = -500 CHF => the project will cost more than planned.

As a conclusion for this sample project, you are now able to give an objective status:
  • Project is over budget, we spend more than planned.
  • Project is ahead of schedule, we produce quicker.
  • Project will cost 500CHF more than planned at the end, but we will probably deliver earlier
In such case, we could imagine that removing a resource inside the team will reduce the cost, and even if it delays the time we have enough margins for this.
As a general conclusion on EVM systems, to be effective, the system should be put in place as earlier as possible. An established baseline must exist (based on scope analysis and the Work Breakdown Structure) against which to measure progress.
EVM also contains more indicators such as To Complete Performance Index, the TCPI (which provides a projection of the anticipated performance required to achieve either the BAC or the EAC). Project managers can also perform variance analysis and management and contingency reserve analysis (trend analysis).

To summarize, this table contains useful formulas for EVM


Post a Comment

Note: Only a member of this blog may post a comment.

Getting started with jQuery

No comments
What is jQuery
jQuery is a JravaScript based framework that emphasizes interaction between JavaScript and HTML. The main goal is to simplify common commands of JavaScript. It was first realized in the beginning of 2006. jQuery is free, open source software Dual-licensed under the MIT License and the GNU General Public License.
The jQuery file can be downloaded here (
jQuery contains the following features:
  • DOM (Document Object Model) element selections using the cross-browser open source selector engine Sizzle, a spin-off out of jQuery project
  • DOM traversal and modification (including support for CSS 1-3 and basic XPath)
  • Events
  • CSS (Cascading Style Sheet) manipulation
  • Effects and animations
  • Ajax (asynchronous JavaScript and XML)
  • Extensibility
  • Utilities – such as browser version and the each function.
  • JavaScript Plug-ins
Microsoft has announced plans to bundle jQuery initially in Visual Studio for use within ASP.NET AJAX framework and ASP.NET MVC Framework.

Examples: Access the selection in an HTML drop down list
jQuery usually exists as a single JavaScript file, containing all the common DOM, Event, Effects, and Ajax functions. It can be included within a web page using the following mark-up:
Suppose we have the following drop down list mark-up, where Vegetable has been selected

How can we use JQuery to determine the text of the selected option (Olympique de Marseille)?
One way is to use an xpath-like expression:
The #clubs tells us to go to the XML node with id = “clubs”. The option tells us to find all tags under categories with tag-name option.  The [@selected] tells us to find the option tag containing an attribute with a key called selected. The text function tells JQuery to return the text in the selected option tag (which in this case is Olympique de Marseille)
Another way to get the option text is to use the built in JQuery filter for getting the selection
A third way to get the option text is to use the find function on the clubs node:
The find function searches for the node within clubs containing the criteria, which in this case is a selected option.
For more information about jQuery, check their website at

No comments :

Post a Comment

Note: Only a member of this blog may post a comment.

Why project management matters?

This is an article I initialy wrote for my company blog.

Sources of project failure
After several surveys, it appears that the main cause of project failure is the lack of project management methodology.
Presented in the form of a Pareto chart, here are the main causes of project failure.
The purpose of the Pareto chart is to highlight the most important among a set of factors. In quality control, it often represents the most common sources of defects, the highest occurring type of defect, or the most frequent reasons for customer complaints.

Reading this results show us that the most important phase in project management are “Initiating” and “Planning” phases.
Necessity of using Project management methodology
In the past we believed that project management would require more people and increase the overall cost in the organization. Today we know that project management allows us to accomplish more work in less time with fewer people and without any sacrifice to quality.
We also believed that only big project requires project management. Today we know that all projects will benefit from project management (maybe not the entire methodology for smaller projects…).
There is no “one best project management methodology”. Each company should build its own methodology based on their business and their maturity in project management. On the market today there is, among others, two methodologies, PRINCE2® and PMBOK® from the Project Management Institute.
Main differences are listed here:

Today, the definition of success is: providing value to customer inside the triple constraint of Scope, Time, and Cost (Quality can also be considered within it).
Project Management methodology is just a tool that will help you on this way.


Post a Comment

Note: Only a member of this blog may post a comment.

SQL Transaction isolation level, with SQL Server 2008

1 comment
As a quick reminder for the 70-433 TS: Microsoft SQL Server 2008, Database Development certification, here is the list of SQL 2008 transaction isolation levels:

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads.

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction.

Specifies the following:
- Statements cannot read data that has been modified but not yet committed by other transactions.
- No other transactions can modify data that has been read by the current transaction until the current transaction completes.
- Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

1 comment :

Post a Comment

Note: Only a member of this blog may post a comment.