One of the main features of XML is that it handles hierarchical data structures. I agree that so does SQL either using the hierarchy variable or creating a Recursive CTE. but nothing compares to the simplicity of the XML format to show data. the following snippet of XML is a Geographical script I occasionally use. The Highest level of the Tree is the Country Data, Then branched off this is Region information and bellow this is the town/village information.
The position in the tree is pivotal to the Area that it describes. however the data that each level is related to the branch above. i.e. Cardiff is in Wales.
<Areas AreaName=”Wales”>
<Area AreaName=”South Wales” GridRef=”123994″>
<Area AreaName=”Cardiff” GridRef=”332233″/>
<Area AreaName=”Pontypool” GridRef=”545676″/>
<Area AreaName=”Newport” GridRef=”123322″/>
<Area AreaName=”Cwmbran” GridRef=”123432″/>
</Area>
</Areas>
Now this is only a small fragment of a much larger document (and made up data ). but the examples should still work.
If I were to search for a Specific Town/City (specific to the location in the XML document) with an AreaName of ‘Cardiff” I could use
SELECT @XML.value(‘(/Areas/Area/Area[@AreaName =”Cardiff”]/@GridRef)[1]’,varchar(50)’)
This would specifically find the 2nd Area Level of Cardiff. But if I didn’t care what level the area was at Town or Region or even country I could change the query to…
SELECT @XML.value(‘(//Area[@AreaName=”Cardiff”]/@GridRef)[1],’varchar(50)’)
This query will return the grid reference for the first Torfean Element that it encounters regardless of the level.
This is an expensive query and should be considered very carefully before being used in a production environment.