Introduction to Formatting Query Results as XML in Microsoft SQL Server 2008

We can use the FOR XML clause with SELECT statements in Microsoft SQL Server to produce XML output from our queries.  We might use this feature when creating output that will be consumed by an application that understands XML.  This post will demonstrate using this feature to create XML output of a contact list.


Here is the T-SQL used to create and populate our Contacts table:

CREATE TABLE Contacts
(
ContactId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
PhoneNumber VARCHAR(20) NOT NULL,
EmailAddress VARCHAR(50) NOT NULL
);

INSERT INTO Contacts ( FirstName, LastName, PhoneNumber, EmailAddress )
VALUES ( 'Smith', 'Jon', '254-555-1234', 'jon@example.com' );


INSERT INTO Contacts ( FirstName, LastName, PhoneNumber, EmailAddress )
VALUES ( 'Rogers', 'Amanda', '254-555-4321', 'amanda@example.com' );


INSERT INTO Contacts ( FirstName, LastName, PhoneNumber, EmailAddress )
VALUES ( 'Gomez', 'Paul', '254-555-5252', 'paul@example.com' );


INSERT INTO Contacts ( FirstName, LastName, PhoneNumber, EmailAddress )
VALUES ( 'Peterson', 'James', '254-555-9876 ext 234', 'james@example.com' );

Let us assume that we have a contact management application that we wish to export this data to and that this fictitious application can import XML that is formatted like the sample below.


<?xml version="1.0"?>
<Contact-List>
<Contact>
<First-Name>John</First-Name>
<Last-Name>Doe</Last-Name>
<Telephone>800-555-1111</Telephone>
<E-mail>doe.john@example.com</E-mail>
</Contact>
<Contact>
<First-Name>Tommy</First-Name>
<Last-Name>Atkins</Last-Name>
<Telephone>800-555-2222</Telephone>
<E-mail>atkins.tommy@example.com</E-mail>
</Contact>
</Contact-List>


As you can see in addition to formatting the output as XML we will also need to map our table's columns to the XML format's elements: "FirstName" in the table will be "First-Name" in the XML.   We can use the FOR XML clause in our SELECT statement to shape the query's result to match the required format.  Below is the T-SQL used to create the XML.

SELECT 

FirstName AS 'First-Name',

LastName AS 'Last-Name',

PhoneNumber AS Telephone,

EmailAddress AS 'E-mail'

FROM Contacts

ORDER BY LastName, FirstName

FOR XML PATH('Contact'), ROOT('Contact-List');

Here is the result of that query.

<Contact-List>

  <Contact>

    <First-Name>Rogers</First-Name>

    <Last-Name>Amanda</Last-Name>

    <Telephone>254-555-4321</Telephone>

    <E-mail>amanda@example.com</E-mail>

  </Contact>

  <Contact>

    <First-Name>Peterson</First-Name>

    <Last-Name>James</Last-Name>

    <Telephone>254-555-9876 ext 234</Telephone>

    <E-mail>james@example.com</E-mail>

  </Contact>

  <Contact>

    <First-Name>Smith</First-Name>

    <Last-Name>Jon</Last-Name>

    <Telephone>254-555-1234</Telephone>

    <E-mail>jon@example.com</E-mail>

  </Contact>

  <Contact>

    <First-Name>Gomez</First-Name>

    <Last-Name>Paul</Last-Name>

    <Telephone>254-555-5252</Telephone>

    <E-mail>paul@example.com</E-mail>

  </Contact>

</Contact-List>

The FOR XML clause instructs the server to transform the results to XML.  The PATH parameter specifies that each record should be wrapped in an element named "Contact".  The ROOT parameter specifies that the result set should be wrapped in an element names "Contact-List".  Note that since the elements contain dashes and dashes are not permitted in column names an alias is needed in our query to name the elements to match the XML elements.   Note that element names in query such as 'First-Name' can be escaped by single quotes, square brackets, or double quotes.  All of the following are equivlement: 'First-Name', "First-Name", [First-Name].

The FOR XML has additional parameters that can be used to control the format of the output.  This post is intended as a basic introduction.

References

"Basic Syntax of the FOR XML Clause" from SQL Server Books Online

http://msdn.microsoft.com/en-us/library/ms190922.aspx

HTML 5 Structure

HTML 5 came out with a whole new set of elements to make structuring a web page much easier. When marking up a page in HTML 4 most of the structure was contained within div elements.

Current versions of HTML 4 do not give us the ability to describe these parts more accurately. HTML 5 addresses this by giving us new elements that correspond with each of the basic layouts of a web page.

The markup would look like this:

<html>
<header>
<title>...</title>
</header>
<body>
<header>...</header>
<nav>...</nav>
<aside>...</aside>
<article>
<section>...</section>
</article>
<footer>...</footer>
</body>
</html>

For more information on the differences between HTML 4 and HTML 5 take a look at W3C

Singleton Design Pattern

Sometimes you need to guarantee that only one instance of a class is created. One example might be a database object where you only want one connection. This class should be available globally, but should only have one instance. The Singleton creational pattern conforms to this requirement.

The O'Reilly book Head First Design Patterns By Eric and Elisabeth Freeman define the Singleton Pattern as a class that has only one instance and provides a global point of access to it.

The classic book Design Patterns by Gamma, Helm, Johnson and Vlissides (Gang of Four) add the following points.

Participants

• Singleton

1. Defines an Instance operation that lets clients access it unique instance. Instance is a class operation.
2. May be responsible for creating its own unique instance.

For my example, I'll create a singleton class in c# using the .Net Framework 4.0 that has a connection object in it. First, start a new console app project. Next, add a new class and name it Singleton.cs with following code...

namespace SingletonExample
{
public sealed class Singleton
{
private static readonly SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=Chinook;Integrated Security=SSPI;");

static Singleton()
{
}

Singleton()
{
}

public static SqlConnection Connection
{
get
{
if (connection.State == ConnectionState.Closed) connection.Open();
return connection;
}
}
}
}

Now, in your program.cs file add the following code to Main...

static void Main(string[] args)
{
SqlConnection connection1 = singleton.connection;
SqlConnection connection2 = singleton.connection;
SqlConnection connection3 = singleton.connection;
SqlConnection connection4 = singleton.connection;
SqlConnection connection5 = singleton.connection;

Console.ReadLine();
}

As you step through the code run sp_who2 on the Chinook database and you will see only one connection added.

By the way, the Chinook database is on Codeplex and is designed as alternative to Northwind. It's small, will run on SQL Server, Oracle and MySQL and installs with a single script.

Enforcing Uniqueness on Optional Fields in SQL Server Database Tables

Enforcing the uniqueness of data in a table is a common requirement.  For example an "Employees" table might contain an "EmployeeID" field that must uniquely identify each row.  This is usually handled by creating a primary key or unique constraint on the field.  This works well when the field in question is required.  In our example each employee is assigned an employee ID number.  Sometimes however this is not sufficient to cover all circumstances.  Let us assume that each employee may be assigned a company email address, but not all employees will be given an email address.  No two employees can share the same email address.  We want the "EmailAddress" field in the Employees table either to be empty or contain a value not already in the table.  We can enforce the uniqueness of the email address values either by using an indexed view or by creating an index.

Here is the T-SQL used to create the Employees table we will use for our examples.

CREATE TABLE Employees

(

EmployeeID CHAR(4) NOT NULL PRIMARY KEY,

LastName VARCHAR(20) NOT NULL,

FirstName VARCHAR(20) NOT NULL,

EmailAddress VARCHAR(30) NOT NULL DEFAULT ''  

);

Option 1: Using an Indexed View

We can create a view of Employee records with an email address then create a unique index on the EmailAddress field.  If a user tries to insert a duplicate email address the insert will fail.

Here is the T-SQL used to create the view and index.   Note that the view only contains records which have a non-empty email address.

CREATE VIEW EmployeeEmailAddresses WITH SCHEMABINDING

AS

SELECT EmailAddress

FROM dbo.Employees

WHERE EmailAddress != '';

GO

CREATE UNIQUE CLUSTERED INDEX Index_EmployeeEmailAddresses_EmailAddress

ON EmployeeEmailAddresses ( EmailAddress );

We can then insert some records into the Employees table using the T-SQL statements below.

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E100', 'Smith', 'Jon', 'smith@example.com' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E200', 'Pond', 'Amy', '' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E300', 'Noble', 'Donna', 'noble@example.com' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E400', 'Jones', 'Martha', '' );

-- this insert will fail

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E500', 'Smith', 'Sarah-Jane', 'smith@example.com' );

The last insert will fail because a record already exists with the email address "smith@example.com".  This approach will work in both SQL Server 2005 and 2008.

Option 2: Create an Index

SQL Server 2008 introduced the WHERE clause into the syntax for the CREATE INDEX statement.  We can create a unique index on records which are not empty with the T-SQL statement below.

CREATE UNIQUE INDEX Index_Employees_EmailAddress

ON Employees ( EmailAddress )

WHERE ( EmailAddress != '' );

To test this index first drop the indexed view and empty the Employees table, then insert our test data again.  The last insert will fail.

-- this will drop the indexed view we created earlier if it exists

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'EmployeeEmailAddresses' )

BEGIN

DROP VIEW EmployeeEmailAddresses;

END;

--  empty the Employees table

DELETE 

FROM Employees;

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E100', 'Smith', 'Jon', 'smith@example.com' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E200', 'Pond', 'Amy', '' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E300', 'Noble', 'Donna', 'noble@example.com' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E400', 'Jones', 'Martha', '' );

-- this insert will fail

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E500', 'Smith', 'Sarah-Jane', 'smith@example.com' );

This technique is new to SQL Server 2008 and is not backwards compatible.  

For more information on each approach consult the SQL Server documentation.

References

Create View from SQL Server Books Online:

http://msdn.microsoft.com/en-us/library/ms187956.aspx

Create Index from SQL Server Books Online:

http://msdn.microsoft.com/en-us/library/ms188783.aspx

Using SET FMTONLY OFF in the Query string of Report Server

By default FMTONLY is set to ON in Report Server. When using a SQL statement containing temp tables an error message commonly occurs. You may get an message like "There is an error in the query. Invalid object name '#myTempTable'".



To fix this I’ve used the command SET FMTONLY OFF at the top of my SQL statement. This allows the SQL statement to return rows with data. The SET FMTONLY ON will only return column information.





Use the following SQL statement, changing the FMTONLY from ON to OFF, in Query Analyzer to see how this works.

USE AdventureWorks;
GO
SET FMTONLY ON;
GO
SELECT *
FROM HumanResources.Employee;
GO
SET FMTONLY OFF;
GO