Guide for Developers: November 2013

Tuesday, 19 November 2013

XML and XSL Tutorial

XML and XSL Tutorial with Example..

Tutorial1.xml

<?xml version="1.0" encoding="utf-8" ?>
<?xml-stylesheet type="text/xsl" href="tvshows.xsl" ?>
<TvShows>
<Shows>
<name>Utaran</name>
<release>2005</release>
<enddate>2009</enddate>
<network country="India">Star Plus</network>
<poster href="http://www.cinejosh.com/gallereys/bollywood/normal/uttaran_serial_1000_episodes_completion_party_0911120942/uttaran_serial_1000_episodes_completion_party_0911120942_034.jpg"></poster>
<viewers units="millions">7.82</viewers>
</Shows>
<Shows>
    <name>Dance India Dance</name>
    <release>2006</release>
    <enddate>2013</enddate>
    <network country="India">Colors</network>
    <poster href="http://upload.wikimedia.org/wikipedia/en/thumb/4/45/Dance_India_Dance_Judges.jpg/290px-Dance_India_Dance_Judges.jpg"></poster>
    <viewers units="millions">15.82</viewers>
</Shows>
<Shows>
    <name>Kahaani Ghar Ghar Ki</name>
    <release>2007</release>
    <enddate>2013</enddate>
    <network country="India">Colors</network>
    <poster href="http://media.startv.in/newstream/star/starplus/kahani_ghar_ghar_ki/1651/kahani_ghar_ghar_ki_ep1651_300x200.jpg"></poster>
    <viewers units="millions">2.82</viewers>
</Shows>

</TvShows>

TVShows.XSL File

<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="html"/>
<xsl:template match="/">

    <html>
      <head>
        <title>TV Shows</title></head>
      <body>
        Version: <xsl:value-of select="system-property('xsl:version')"/><br />
        Vendor: <xsl:value-of select="system-property('xsl:vendor')"/><br />
        Vendor URL: <xsl:value-of select="system-property('xsl:vendor-url')"/><br />

        <xsl:for-each select="TvShows/Shows">

          <a href="#{generate-id(name)}">
            <xsl:value-of select="name"/>
          </a><br/>
    </xsl:for-each>

        <xsl:for-each select="TvShows/Shows">
          <xsl:sort select="name" order="ascending" data-type="text"/>
          <h3>
            <a name="{generate-id(name)}">
              <xsl:value-of select="name"/>
            </a>
          </h3>
          <img>
            <xsl:attribute name="src">
              <xsl:value-of select="poster/@href"/>
            </xsl:attribute>
          </img>
          <br/>

          <xsl:value-of select="current()"/> <br/>

          <p> The show <xsl:value-of select="name"/> was
          released in <xsl:value-of select="release"/> by
          <xsl:value-of select="network"/>. It had an average viewership
          of <xsl:value-of select="viewers"/> million people. It was
          cancelled in <xsl:value-of select="enddate"/>
        </p> <br/>
   </xsl:for-each>

        <table border="2">
          <tr><th>Name</th><th>Network</th><th>Viewers</th></tr>
          <xsl:for-each select="TvShows/Shows">
            <xsl:if test="release < 2006">
              <tr bgcolor="red">
                <td><xsl:value-of select="name"/></td>
                <td><xsl:value-of select="network"/></td>
                <td><xsl:value-of select="viewers"/></td>
              </tr>
            </xsl:if>
              <xsl:choose>

                <xsl:when test="release>2006">
                  <tr bgcolor="yellow">
                      <td><xsl:value-of select="name"/></td>
                      <td><xsl:value-of select="network"/></td>
                      <td><xsl:value-of select="viewers"/></td>
                   </tr>
                </xsl:when>

                <xsl:when test="release=2006">
                  <tr bgcolor="orange">
                    <td><xsl:value-of select="name"/></td>
                    <td><xsl:value-of select="network"/></td>
                    <td><xsl:value-of select="viewers"/></td>
                  </tr>
                </xsl:when>
                <xsl:otherwise>
                  <tr bgcolor="pink">
                    <td><xsl:value-of select="name"/></td>
                    <td><xsl:value-of select="network"/></td>
                    <td><xsl:value-of select="viewers"/></td>
                  </tr>
             </xsl:otherwise>
              </xsl:choose>
           </xsl:for-each>

        </table>

      </body>
    </html>

</xsl:template>
</xsl:stylesheet>

OUTPUT

Version: 1.0
Vendor: libxslt
Vendor URL: http://xmlsoft.org/XSLT/
Utaran
Dance India Dance
Kahaani Ghar Ghar Ki

Dance India Dance

Dance India Dance 2006 2013 Colors 15.82

The show Dance India Dance was released in 2006 by Colors. It had an average viewership of 15.82 million people. It was cancelled in 2013

Kahaani Ghar Ghar Ki

Kahaani Ghar Ghar Ki 2007 2013 Colors 2.82

The show Kahaani Ghar Ghar Ki was released in 2007 by Colors. It had an average viewership of 2.82 million people. It was cancelled in 2013

Utaran

Utaran 2005 2009 Star Plus 7.82

The show Utaran was released in 2005 by Star Plus. It had an average viewership of 7.82 million people. It was cancelled in 2009

Name	Network	Viewers
Utaran	Star Plus	7.82
Utaran	Star Plus	7.82
Dance India Dance	Colors	15.82
Kahaani Ghar Ghar Ki	Colors	2.82

Sunday, 10 November 2013

COALESCE (Transact-SQL)

SQL Server 2012

Other Versions

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

Applies to: SQL Server (SQL Server 2008 through current version), Windows Azure SQL Database (Initial release through current release).

Transact-SQL Syntax Conventions

Syntax

COALESCE ( expression [ ,...n ] )

Arguments

expression: Is an expression of any type.

Return Types

Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.

Remarks

If all arguments are NULL, COALESCE returns NULL. At least one of the null values must be a typed NULL.

Comparing COALESCE and CASE

The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:
CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   WHEN (expression2 IS NOT NULL) THEN expression2
   ...
   ELSE expressionN
END
This means that the input values (expression1, expression2, expressionN, etc.) will be evaluated multiple times. Also, in compliance with the SQL standard, a value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. In either case, different results can be returned between the first evaluation and subsequent evaluations.
For example, when the code COALESCE((subquery), 1) is executed, the subquery is evaluated twice. As a result, you can get different results depending on the isolation level of the query. For example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. To ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESE with the ISNULL function. Alternatively, you can rewrite the query to push the subquery into a subselect as shown in the following example.

SELECT CASE WHEN x IS NOT NULL THEN x ELSE 1 END
FROM
(
SELECT (SELECT Nullable FROM Demo WHERE SomeCol = 1) AS x
) AS T;

Comparing COALESCE and ISNULL

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.
Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.
The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example.
USE tempdb; GO -- This statement fails because the PRIMARY KEY cannot accept NULL values -- and the nullability of the COALESCE expression for col2 -- evaluates to NULL. CREATE TABLE #Demo ( col1 integer NULL, col2 AS COALESCE(col1, 0) PRIMARY KEY, col3 AS ISNULL(col1, 0) ); -- This statement succeeds because the nullability of the -- ISNULL function evaluates AS NOT NULL. CREATE TABLE #Demo ( col1 integer NULL, col2 AS COALESCE(col1, 0), col3 AS ISNULL(col1, 0) PRIMARY KEY );
Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int whereas for COALESCE, you must provide a data type.
ISNULL takes only 2 parameters whereas COALESCE takes a variable number of parameters.

Examples

A. Running a simple example

The following example shows how COALESCE selects the data from the first column that has a nonnull value. This example uses the AdventureWorks2012 database.

SELECT Name, Class, Color, ProductNumber,
COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product;

B. Running a complex example

In the following example, the wages table includes three columns that contain information about the yearly wages of the employees: the hourly wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage, salary, and commission.

SET NOCOUNT ON;
GO
USE tempdb;
IF OBJECT_ID('dbo.wages') IS NOT NULL
    DROP TABLE wages;
GO
CREATE TABLE dbo.wages
(
    emp_id        tinyint   identity,
    hourly_wage   decimal   NULL,
    salary        decimal   NULL,
    commission    decimal   NULL,
    num_sales     tinyint   NULL
);
GO
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)
VALUES
    (10.00, NULL, NULL, NULL),
    (20.00, NULL, NULL, NULL),
    (30.00, NULL, NULL, NULL),
    (40.00, NULL, NULL, NULL),
    (NULL, 10000.00, NULL, NULL),
    (NULL, 20000.00, NULL, NULL),
    (NULL, 30000.00, NULL, NULL),
    (NULL, 40000.00, NULL, NULL),
    (NULL, NULL, 15000, 3),
    (NULL, NULL, 25000, 2),
    (NULL, NULL, 20000, 6),
    (NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52, 
   salary, 
   commission * num_sales) AS money) AS 'Total Salary' 
FROM dbo.wages
ORDER BY 'Total Salary';
GO

Here is the result set.
Total Salary
------------
10000.00
20000.00
20800.00
30000.00
40000.00
41600.00
45000.00
50000.00
56000.00
62400.00
83200.00
120000.00
(12 row(s) affected)

Reference

ISNULL (Transact-SQL)

CASE (Transact-SQL)