Solving a Confusing MigrationBlocker While Moving to Azure SQL Database

This week, I’m altering functions and stored procedures in a SQL Server 2008 database so that it can be migrated to Azure. The Data Migration Assistant does a great job of generating reports identifying MigrationBlockers, but one type of error was vague enough to confuse me for a few minutes. Here’s an example of that error:

    {
      "Recommendations": [
        {
          "ApplicableCompatibilityLevels": [
            "CompatLevel100",
            "CompatLevel110",
            "CompatLevel120",
            "CompatLevel130",
            "CompatLevel140"
          ],
          "ChangeCategory": "MigrationBlocker",
          "RuleId": "46010",
          "Title": "One or more objects contain statements that are not supported in Azure SQL Database [46010]",
          "Impact": "While assessing the schema on the source database, one or more syntax issues were found. Syntax issues on the source database indicate that some objects contain syntax that is unsupported in Azure SQL Database.",
          "ImpactDetail": "Function: [dbo].[GalleryXML] contains a statement that is not supported on Microsoft Azure SQL Database v12. The specific error is: Incorrect syntax near AS.",
          "Recommendation": "Note that some of these syntax issues may be reported in more detail as separate issues in this assessment.  Review the list of objects and issues reported, fix the syntax errors, and re-run assessment before migrating this database.",
          "MoreInfo": ""
        }
      ],
      "IsSelectedForMigration": true,
      "Eligibility": {
        "IsEligibleForMigration": true,
        "Explanation": "OK"
      },
      "ObjectName": "GalleryXML",
      "SchemaName": "dbo",
      "ObjectType": "UserDefinedFunction"
    },

The error text, “Incorrect syntax near AS,” was just not a strong clue as to what was wrong with this function. The purpose of the function is to create structured XML that can be de-serialized into an instance of an object in C#. I use PATH Mode to accomplish this, and it is one of my favorite maneuvers when operating in SQL Server/.Net. Here is an abbreviated version of the query to help you understand the required changes to continue using this method in an Azure database:

SELECT 
ID,

...

--Subquery for photos
(
	SELECT 
	ID,

	...

	FROM GalleryPhotosTbl 
	WHERE GalleryID = GalleryTbl.ID
	FOR XML PATH('GalleryPhoto'), TYPE
) AS Photos
FROM GalleryTbl
WHERE GalleryTbl.ID = @ID
FOR XML PATH('Gallery'), TYPE

The syntax that Azure doesn’t support is “AS Photos.” Here is the solution:

SELECT 
ID,

...

--Subquery for photos
(
	SELECT 
	ID,

	...

	FROM GalleryPhotosTbl 
	WHERE GalleryID = GalleryTbl.ID
	FOR XML PATH('GalleryPhoto'), root ('Photos'), TYPE
)
FROM GalleryTbl
WHERE GalleryTbl.ID = @ID
FOR XML PATH('Gallery'), TYPE