Given a query, generate the resulting Biml table

TableViaQuery.biml

<#@ import namespace="System.Data" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#
	Dictionary<string, string> namedQueries = new Dictionary<string,string>{{"Query 28", @"-- Drive level latency information (Query 28) (Drive Level Latency)
-- Based on code from Jimmy May
SELECT tab.[Drive], tab.volume_mount_point AS [Volume Mount Point], 
    CASE 
        WHEN num_of_reads = 0 THEN 0 
        ELSE (io_stall_read_ms/num_of_reads) 
    END AS [Read Latency],
    CASE 
        WHEN num_of_writes = 0 THEN 0 
        ELSE (io_stall_write_ms/num_of_writes) 
    END AS [Write Latency],
    CASE 
        WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 
        ELSE (io_stall/(num_of_reads + num_of_writes)) 
    END AS [Overall Latency],
    CASE 
        WHEN num_of_reads = 0 THEN 0 
        ELSE (num_of_bytes_read/num_of_reads) 
    END AS [Avg Bytes/Read],
    CASE 
        WHEN num_of_writes = 0 THEN 0 
        ELSE (num_of_bytes_written/num_of_writes) 
    END AS [Avg Bytes/Write],
    CASE 
        WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 
        ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes)) 
    END AS [Avg Bytes/Transfer]
FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, SUM(num_of_reads) AS num_of_reads,
             SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
             SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
             SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall, vs.volume_mount_point 
      FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
      INNER JOIN sys.master_files AS mf WITH (NOLOCK)
      ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
      CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS vs 
      GROUP BY LEFT(UPPER(mf.physical_name), 2), vs.volume_mount_point) AS tab
ORDER BY [Overall Latency] OPTION (RECOMPILE);"}};
#>
	
	<Tables>
		<# foreach(var kvp in namedQueries){ #>
		<#=  GetAstTableNodeFromQuery(this.RootNode.OleDbConnections["msdb"], kvp.Value, "dbo", kvp.Key).GetBiml() #>
		<# } #>
		
    </Tables>
	
</Biml>

<#+
	
    /// <summary>
    /// Build out a Biml table based on the supplied query and connection. 
	/// This assumes a valid SQL Server 2012+ OLEDB Connection is provided but the approach
	/// can be adapted based on providers and information schemas.
	/// We further assume that column names in the query are unique.
    /// </summary>
    /// <param name="connection">An OleDbConnection</param>
    /// <param name="query">A SQL query</param>
    /// <param name="schemaName">The schema our table should be created in</param>
    /// <param name="queryName">A name for our query</param>
    /// <returns>Best approximation of a SQL Server data type</returns>
	public AstTableNode GetAstTableNodeFromQuery(AstOleDbConnectionNode connection, string query, string schemaName, string queryName)
	{
		string template = @"SELECT
    DEDFRS.name
,   DEDFRS.is_nullable
,   DEDFRS.system_type_name
,   DEDFRS.max_length
,   DEDFRS.precision
,   DEDFRS.scale
FROM
    sys.dm_exec_describe_first_result_set(N'{0}', NULL, NULL) AS DEDFRS ORDER BY DEDFRS.column_ordinal;";
		AstTableNode atn = null;

		atn = new AstTableNode(null);
		atn.Name = queryName;
		atn.Schema = this.RootNode.Schemas[schemaName];
		string queryActual = string.Format(template, query.Replace("'", "''"));
		
		string colName = string.Empty;
		string typeText = string.Empty;
		System.Data.DbType dbt = DbType.UInt16;
		int length = 0;
		int precision = 0;
		int scale = 0;

		try
		{
			System.Data.DataTable dt = null;
            dt = ExternalDataAccess.GetDataTable(connection, queryActual);
            foreach (System.Data.DataRow row in dt.Rows)
            {
				try
				{
	                AstTableColumnBaseNode col = new AstTableColumnNode(atn);
					// This can be empty -- see DBCC TRACESTATUS (-1)
					if(row[0] == DBNull.Value)
					{
						atn.Annotations.Add(new AstAnnotationNode(atn){Tag = "Invalid", Text = "No Metadata generated"});
						break;
                    }
					else
					{
						colName = row[0].ToString();
					}
					
					typeText = row[2].ToString();
					dbt = TranslateSqlServerTypes(row[2].ToString());
					length = int.Parse(row[3].ToString());
					precision = int.Parse(row[4].ToString());
					scale = int.Parse(row[5].ToString());
					
					col.Name = colName;
					col.IsNullable = (bool)row[1];
					col.DataType = dbt;
					col.Length = length;
					col.Precision = precision;
					col.Scale = scale;
					
					atn.Columns.Add(col);
				}
				catch (Exception ex)
				{
					// Something went awry with making a column for our table
	                AstTableColumnBaseNode col = new AstTableColumnNode(atn);
					col.Name = "FailureColumn";
					col.Annotations.Add(new AstAnnotationNode(col){Tag = "colName", Text = colName});
					col.Annotations.Add(new AstAnnotationNode(col){Tag = "typeText", Text = typeText});
					col.Annotations.Add(new AstAnnotationNode(col){Tag = "dbtype", Text = dbt.ToString()});
					col.Annotations.Add(new AstAnnotationNode(col){Tag = "Error", Text = ex.Message});
					col.Annotations.Add(new AstAnnotationNode(col){Tag = "Stack", Text = ex.StackTrace});
					atn.Columns.Add(col);
                }
            }
        }
		catch (Exception ex)
		{
			// Table level failures
            AstTableColumnBaseNode col = new AstTableColumnNode(atn);
			col.Name = "Failure";
			col.Annotations.Add(new AstAnnotationNode(col){Tag = "Error", Text = ex.ToString()});
			col.Annotations.Add(new AstAnnotationNode(col){Tag = "SourceQuery", Text = query});
			col.Annotations.Add(new AstAnnotationNode(col){Tag = "QueryActual", Text = queryActual});
			atn.Columns.Add(col);
        }
		return atn;
    }
	
    /// <summary>
    /// A rudimentary method to convert SQL Server data types to Biml types. Doesn't cover
	/// UDDT, sql_variant(well)
    /// </summary>
    /// <param name="typeName">Data type with optional length/scale/precision</param>
    /// <returns>Best approximation of a SQL Server data type</returns>
	public DbType TranslateSqlServerTypes(string typeName)
	{
		// typeName might contain length - strip it
		string fixedName = typeName;
		if(typeName.Contains("("))
		{
			fixedName = typeName.Substring(0, typeName.IndexOf("("));
        }
		// Approximate translation of https://msdn.microsoft.com/en-us/library/System.Data.DbType.aspx
		// https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings
		Dictionary<string, DbType> translate = new Dictionary<string, DbType> {
			
		    {"bigint", DbType.Int64 }
		,   {"binary", DbType.Binary }
		,   {"bit", DbType.Boolean }
		,   {"char", DbType.AnsiStringFixedLength }
		,   {"date", DbType.Date }
		,   {"datetime", DbType.DateTime }
		,   {"datetime2", DbType.DateTime2 }
		,   {"datetimeoffset", DbType.DateTimeOffset }
		,   {"decimal", DbType.Decimal }
		,   {"float", DbType.Double }
		//,   {"geography", 
		//,   {"geometry", 
		//,   {"hierarchyid", 
		,   {"image", DbType.Binary }
		,   {"int", DbType.Int32 }
		,   {"money", DbType.Decimal }
		,   {"nchar", DbType.StringFixedLength }
		,   {"ntext", DbType.String }
		,   {"numeric", DbType.Decimal }
		,   {"nvarchar", DbType.String }
		,   {"real", DbType.Single }
		,   {"smalldatetime", DbType.DateTime }
		,   {"smallint", DbType.Int16 }
		,   {"smallmoney", DbType.Decimal }
		,   {"sql_variant", DbType.Object }
		,   {"sysname", DbType.String }
		,   {"text", DbType.String }
		,   {"time", DbType.Time }
		,   {"timestamp", DbType.Binary }
		,   {"tinyint", DbType.Byte }
		,   {"uniqueidentifier", DbType.Guid }
		,   {"varbinary", DbType.Binary }
		,   {"varchar", DbType.AnsiString }
		,   {"xml", DbType.Xml }
        };
		
		try
		{
			return translate[fixedName];
        }
		catch
		{
			return System.Data.DbType.UInt64;
        }
    }
#>