Bloggers at SQLblog.com were occasionally having issues posting. We had narrowed down the cause to the use of bracketed identifiers with certain text, such as [name] or [date], so that when a post contained a query such as this:

I laughed, I cried...

http://www.youtube.com/watch?v=v5uw07iEkjU

Bill Gates presents a farewell video at the keynote of the Consumer Electronics Show. (video URL has been changed to YouTube since the MSN video and image are no longer "available".)

While working on a new book, I have been experimenting with the new HIERARCHYID data type. And since I've seen comparisons drawn between using XML versus using HIERARCHYID to manage a hierarchy of data, I decided that it would likely be very useful if I could convert an XML structure into an equivalent relational structure using HIERARCHYID. Here is what I came up with:

DECLARE @x XML =
'<A id="1">
    <B id="2">
      <C id="3"/>
      <D id="4"/>
    </B> 
    <E id="5"/>
    <F id="6"/>
</A>';

WITH Folders (ID, ParentID, Description, RowNum) AS
(
    SELECT
        t.c.value('@id', 'int')
        , NULLIF(t.c.value('../@id', 'nvarchar(50)'), '')
        , t.c.value('local-name(.)', 'nvarchar(50)')
        , t.c.value('for $s in . return count(../*[. << $s]) + 1', 'int')
    FROM @x.nodes('//*') AS t(c)
)
, FolderTree AS
(
    SELECT ID, ParentID, Description, RowNum,
        HIERARCHYID::GetRoot() AS FolderNode
    FROM Folders
    WHERE ParentID IS NULL

    UNION ALL

    SELECT F.ID, F.ParentID, F.Description, F.RowNum,
        CAST(FT.FolderNode.ToString() + CAST(F.RowNum AS varchar(50)) + '/'
            AS HIERARCHYID)
    FROM Folders AS F
        INNER JOIN FolderTree AS FT ON F.ParentID = FT.ID 
)
SELECT
    ID,
    ParentID,
    Description,
    FolderNode,
    FolderNode.ToString() AS Path
FROM FolderTree
ORDER BY FolderNode;

Which results in:

The basis of this is the same as if converting an existing adjacency model to use HIERARCHYID – use a combination of a recursive CTE and row numbers partitioned by parent to generate the new path of the node and then cast that path to the HIERARCHYID data type. The parent path is retrieved using the ToString method of the parent node, and this is concatenated with the RowNum value and a forward slash (/) to result in the path for the current node. And finally, the newly generated path is then cast to HIERARCHYID, which uses its static Parse method behind the scenes to create a new instance of HIERARCHYID. This table shows the additional detail and should reveal how the new path is created.

On the XML side of things, I generated a partitioned row number by using the nodes method to return all nodes in the XML structure, as shown here: FROM @x.nodes('//*') AS t(c). The double forward slash (//) translates to "relative path" and the asterisk means any node, so this returns all nodes at all levels. Then, to return the row number, I use one of my favorite XQuery examples:

t.c.value('for $s in . return count(../*[. << $s]) + 1', 'int') AS RowNum

This translates to "count the number of immediate child nodes of my parent node that are positioned before me." Now that I have all the nodes, and a partitioned row number for these nodes, I can construct the new path of the node.

I will post more as I continue writing about and exploring SQL Server 2008.

Enjoy!

My esteemed colleague Louis Davidson just wrote about a new feature in SQL Server 2008 known as row constructors. I thought I'd follow up with some more fun usage of this cool feature. In addition to being used with an INSERT statement, you can also use a row constructor in a common table expression (CTE). This example uses the row constructor to insert data into one table (@Movie) and then again to join a table to another sets of constructed row:

DECLARE @Movie TABLE
(
    MovieID INT IDENTITY(1, 1),
    MovieRatingId INT,
    Title VARCHAR(200) NOT NULL
);

INSERT INTO @Movie (MovieRatingId, Title)
VALUES
    (3, 'SQL the Movie'),
    (4, 'SQL Massacre'),
    (1, 'SQL for Everyone'),
    (4, 'SQL Massacre 2 - The Oracle Returns');

WITH MovieRatings (MovieRatingId, Code, Description, AllowYouthRentalFlag)
AS
(    SELECT *
    FROM (VALUES
        (0, 'UR','Unrated',1),
        (1, 'G','General Audiences',1),
        (2, 'PG','Parental Guidance',1),
        (3, 'PG-13','Parental Guidance for Children Under 13',1),
        (4, 'R','Restricted, No Children Under 17 without Parent',0))
            AS MR(MovieRatingId, Code, Description, AllowYouthRentalFlag)
)
SELECT
    M.Title,
    MR.Code AS RatingCode,
    MR.Description AS RatingDescription
FROM @Movie AS M
    INNER JOIN MovieRatings AS MR
        ON M.MovieRatingId = MR.MovieRatingId;

Notice that when used in a CTE, the syntax is slightly different

SELECT column_list
FROM
(VALUES
    (column_1_value [, column_2_value [, ...]])
    (column_1_value [, column_2_value [, ...]])
) AS TableAlias (column_1_name [, column_2_name [, ...]])

When used in a CTE, you must supply a table alias, as well as column names for all columns supplied by the row constructor.

Enjoy!

Have you ever needed to pass in a value that would be used throughout the lifetime of a connection - perhaps a connection session level value that would be used by multiple procedures, views, functions, etc...?

Well, a colleague of mine, Jay Frysinger, had a great idea - use the connection string itself to pass additional parameters. You can use both "Application Name" and "Workstation ID" to pass in 2 additional 128 character length strings. You can then use the APP_NAME() system function to retrieve the value of the "Application Name" or the HOST_NAME() system function to retrieve the value of "Workstation ID".

In this example, a GUID is created in the data client, then using ToString() of the GUID, it is added to the connection string. Then,  HOST_NAME() is used to extract that value. Since I wanted a UniqueIdentifier in SQL, I used CAST to convert the string GUID to a UniqueIdentifier. Finally, the UniqueIdentifier is returned and displayed in the console.

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConnectionStringTest
{
    class Program
    {
        static void Main(string[] args)
        {
            Guid guid = new Guid("BCAEF7EA-7BD3-4CC8-8288-9B2C8F6BCF28");

            using (SqlConnection c = new SqlConnection(string.Format
                (@"Data Source=YourServer;Initial Catalog=YourDatabase;Persist Security Info=True;Trusted_Connection=True;Workstation ID={0}", 
                guid.ToString())))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT CAST(HOST_NAME() as uniqueidentifier)", c))
                {
                    cmd.CommandType = CommandType.Text;
                    c.Open();
                    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (dr.Read())
                    {
                        Console.WriteLine(dr.GetSqlGuid(0).ToString());
                    }
                    dr.Close();
                }
                c.Close();
            }
            Console.ReadLine();
        }
    }
}


A little overdue, here are my code samples from my sessions at DevTeach Montreal 2007.

[EDIT] I made a few changes to account for the issues Chris and Cooter noted in the comments. 

One last post before I go on vacation next week...

SQL Server 2005 includes an undocumented function, sys.fn_varbintohexstr, that converts a hex value to a string representation of that hex value (0x3a becomes '0x3a'), but there is no function to go back from a hex string to a hex value. Now there is J:

I was perusing the newsgroups when I came across a request in the microsoft.public.sqlserver.clr newsgroup to see if there was a way to use System.Web.HttpUtility.UrlEncode in T-SQL. I know using the CLR is an option, but I decided to write a T-SQL equivalent. I reflected the code to figure out the exact rules for UrlEncoding. Then, I discovered much to my delight, a new system function in SQL Server 2005 that converts a string to its hexadecimal equivalent as a string– sys.fn_varbintohexstr. I knew there was an extended stored procedure in prior versions of SQL Server to do this conversion, but this function made writing this UrlEncode function even easier.

This function works in a very similar fashion to the UrlEncodeBytesToBytesInternal found in the .NET Framework (in System.Web.HttpUtility) except that the function I wrote does not convert the URL to a byte array. If you have the latest version of Reflector, you can view the reflected code for UrlEncodeBytesToBytesInternal by going to this URL:

code://System.Web:2.0.0.0:b03f5f7f11d50a3a/System.Web.HttpUtility/UrlEncodeBytesToBytesInternal(Byte%5b%5d,Int32,Int32,Boolean):Byte%5b%5d

And speaking of Reflector – if you have the latest version, you can copy a URL link to a method, class, property, and so on by select the item and pressing Ctrl-Alt-C (which is how I got the URL to lookup the UrlEncodeBytesToBytesInternal method in Reflector).

[Edit - I've made some modifications which should address everyone's very valid points!]