Peter W. DeBetta

OT: What will Bill Gates do after he leaves Microsoft?

I laughed, I cried...

http://video.msn.com/video.aspx?mkt=en-US&brand=&vid=27db94c9-4892-4059-8688-f4afd3c4b64a

Bill Gates presents a farewell video at the keynote of the Consumer Electronics Show.

---

Cross-posted from SQLBlog! - http://www.sqlblog.com

---

posted Monday, January 07, 2008 5:06 PM by debettap

TechEd2008 Abstract Deadline Extension

Just over a month ago, Simon Guest had posted a blog entry about the call for sessions for TechEd 2008. The original deadline was 31 December 2007, but I've just gotten confirmation that this deadline has been extended to 31 January 2008, so if you were thinking of submitting any abstracts, you still have some time to do it.

To get started, you need to first go to https://2008.msteched.com/cft/. Use TEUS-Speakers as the access code, create a profile, and begin submitting topics.

---

Cross-posted from SQLBlog! - http://www.sqlblog.com

---

posted Thursday, January 03, 2008 11:28 AM by debettap

2008: Converting XML to HIERARCHYID

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 AS

(

SELECT

t.c.value('@id', 'int') AS ID

, NULLIF(t.c.value('../@id', 'nvarchar(50)'), '') AS ParentID

, t.c.value('local-name(.)', 'nvarchar(50)') AS Description

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

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.