336
Part III: Advanced T-SQL Data Types Querying Techniques
6 269 Marketing Assistant 2
6 271 Marketing Specialist 2
6 272 Marketing Assistant 2
7 37 Production Technician - WC60 4
7 76 Production Technician - WC60 4
7 84 Production Technician - WC60 4
7 122 Production Technician - WC60 4
7 156 Production Technician - WC60 4
7 194 Production Technician - WC60 4
12 3 Engineering Manager 2
14 29 Production Technician - WC50 4
14 67 Production Technician - WC50 4XML
Per the SQL Server documentation, an XML document is a tree, and thus a single XML data
type instance can represent a complete hierarchy. In fact, when creating an XML index in SQL
Server, hierarchyid values are used to represent the position in the hierarchy internally.I know this chapter discusses the Hierarchyid in SQL Server, but it should be noted that there
are indeed times when using the XML data type to store hierarchical information is more
effi cient. However, in order for XML to claim superiority you need to ensure the following:■ (^) The entire hierarchy is always stored and retrieved.
■ (^) The hierarchy data is consumed in XML format by the retrieving application.
■ (^) Predicate searches are very limited, and these searches are not performance
dependent.
For example, we could have a table which looks like the following:
CREATE TABLE OrgXML
(
OrgID int,
OrgData xml
)
Thus, our data would look something like this:
OrgID OrgData
1
1
1
1
1
It would be nice to have the ability to query the hierarchyid data and return it as an
XML document, but unfortunately, right now, it is not possible. You should see that capabil-
ity in the future, but not now.
c13.indd 336c13.indd 336 7/30/2012 4:47:05 PM7/30/2012 4:47:05 PM
http://www.it-ebooks.info