How to use Recursion with FOR XML PATH

If you have tried writing a recursive function using FOR XML EXPLICIT you will know how tedious it can be. However, with FOR XML PATH recursion is simple and incredibly fast. I had to recently write a recurisve function to return all province and city data in Canada in our system as a formatted XML document. The following is just a simple example I wrote to demonstrate how to accomplish this task with FOR XML PATH (NOTE: this function is just a basic example, it is not the full version I wrote for the task I was working on). If you need to write a recursive function, I hope this helps you get started!

This example recursive function takes in a LocationID and produces an XML document with every Location descendant of the given LocationID:

CREATE FUNCTION dbo.GetAllLocationsFromParent(@Parent int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
  (SELECT loc.LocationID as '@id',
	loc.[name] as '@name',
	loct.[Name] as '@type',
      CASE WHEN loc.Parent=@Parent
      THEN dbo. GetAllLocationsFromParent(loc.LocationID) --Recursive call
      END
	FROM dbo.Location loc
		JOIN dbo.LocationType loct ON loct.LocationTypeID = loc.LocationTypeID
	WHERE loc.Parent=@Parent
		and loc.[Status] = 'Approved'
   FOR XML PATH('location'), TYPE)
END
GO

The following is a sample of the format of the XML document if the Parent LocationID passed in was 0 for World, where all countries and their corresponding decendants would be returned:

<location id="1" name="Canada" type="Country">
    <location id="123" name="Saskatchewan" type="State">
      <location id="12345" name="Saskatoon" type="City">
        </location>
        ...
      </location>
      ...
    </location>
    ...
  </location>
   ...
  </location>
  ...

That’s all! Writing a recursive method with FOR XML PATH is very straight forward.