Recursive XML branches

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″/>



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.

This entry was posted in Uncategorized, XML. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s