Part of the promise of XML is that it makes modeling hierarchical data relatively painless. The hierarchical structure of XML automatically provides context to your data. Consider the following simple example:
<General name="Grant"> <Colonel name="Sanders"> <Major name="Payne"> <Captain name="Calamity"> <Sergeant name="Bilko"> <Private name="Benjamin" /> </Sergeant> </Captain> </Major> </Colonel></General>
As you can see, the hierarchical structure is implicit in the structure of the XML. The General is above the Colonel is above the Major, and so on. Organizational charts, military chains of command, and other similar structures are standard fare for hierarchical modeling. Needless to say, the AdventureWorks database has a standard organizational hierarchy modeled as relational data which you'll probably see used in a whole lot of samples all over the place.
But AdventureWorks also includes a more detailed, but often-overlooked, real-world example of hierarchy. The AdventureWorks Bill of Materials (BOM) hierarchy is modeled as an adjacency list hierarchy; one in which the child nodes maintain links to their parent nodes in the same table. You can query the Bill of Materials using the dbo.uspGetBillOfMaterials procedure like this:
EXEC dbo.uspGetBillOfMaterials 749, '2001-01-01'
The results are a "flat" relational listing of the raw materials used to build the "Road-150 Red, 62" bike.
The results do provide the parent and child component ID numbers, as well as the level of the component in the BOM. But they do not convey the hierarchical information without some sort of reconstruction on your part - probably performed in the UI for display purposes. All of the information required to reconstruct the hierarchical structure of the BOM is available in this result set, but it's not readily apparent. You can use FOR XML PATH and the xml data type to reconstruct the hierarchical structure of the BOM with a simple recursive function like this:
CREATE FUNCTION dbo.GetRecursiveBom(@ProductID int, @level int = 0)RETURNS xmlASBEGIN DECLARE @x xml; SET @x = ( SELECT bom.ComponentID AS "@id", p.ProductNumber AS "@number", @level AS "@level", p.Name AS "name", p.Color AS "color", p.ListPrice AS "list-price", bom.PerAssemblyQty AS "quantity", p.Size AS "size", p.SizeUnitMeasureCode AS "unit-of-measure", ( SELECT CASE WHEN @level < 5 THEN dbo.GetRecursiveBom(bom.ComponentID, @level + 1) ELSE '' END ) FROM Production.BillOfMaterials bom INNER JOIN Production.Product p ON bom.ComponentID = p.ProductID WHERE @ProductID = CASE WHEN @level = 1 THEN bom.ComponentId ELSE bom.ProductAssemblyID END FOR XML PATH (N'item'), TYPE ); RETURN @x;ENDGO
This function calls itself recursively to retrieve a BOM (up to 5 levels deep) in XML format. You can call the function like this:
SELECT dbo.GetRecursiveBom (749, 0);
And the resulting XML looks like the image below:
As you can see the item nodes are nested within each other, with each component acting as a container for its constitutent components. The code for this function is available as a download file here: GetRecursiveBom.sql.txt
In a future post we'll look at how to create a "recursive" XML schema to validate the XML produced by this function.
