337
Chapter 13: Working with Hierarchies
13
Although you can’t write a simple query to retrieve XML from hierarchyid data, there
are other options, , but they require a lot of work. There are two ways to compose XML from
the hierarchyid; by looping through the hierarchy structure or by using recursive CTE to
generate a list of nodes and their corresponding hierarchy. Depending on the amount of
data and your hierarchy structure, the two different methods will differ in performance.Peter DeBetta did a write-up on the two different approaches and his fi ndings in speed and
performance, so I’m not going to rehash them here. However, you can fi nd the blog post at
http://sqlblog.com/blogs/peter_debetta/archive/2010/02/17/
converting-hierarchyid-to-xml-in-t-sql.aspx.Summary
The hierarchyid has been around for a few years now, and it really hasn’t changed much
since it was fi rst introduced. Its purpose is simply to represent positions within a hierarchy,
but the key to understanding the hierarchyid data type is understanding that it does not
automatically represent a hierarchy tree. Instead, it is up to the application to generate and
assign the appropriate hierarchy values. The trick is to do this is a way that refl ects the
relationships between the rows accurately.This chapter covered the data type methods that provide effi cient querying of the
hierarchyid data type. However, you also looked briefl y at a well-known alternative to the
hierarchyid data type, XML. XML has been around for a lot longer than the hierarchyid and
has roots deep into describing and shaping data, such as hierarchies.Unfortunately, the two don’t quite mesh yet, meaning that you can’t query the hierarchyid
and simply return a nicely shaped XML document. Regardless, the hierarchyid is an excel-
lent method for storing hierarchical data, and with the supported methods makes is much
more effi cient and a performant solution over XML.c13.indd 337c13.indd 337 7/30/2012 4:47:06 PM7/30/2012 4:47:06 PM
http://www.it-ebooks.info