Sql Server FOR XML AUTO and back with a user defined table type?

Question

I would like to convert the results of a stored procedure returning a specific user defined data type back to a table.

This is based on the idea presented here: http://sqlblogcasts.com/blogs/danny/archive/2008/01/06/for-xml-and-back-again.aspx

So for instance if I have a type declared as:

CREATE TYPE [dbo].[MyType]
AS TABLE 
(
    Id INT IDENTITY,
    MonthNumber INT,     
    YearNumber INT,      
    NumberOfOfficersMakingReferrals INT, 
    TierLevel NVARCHAR(10),                       
    TierStrengthTotal INT                
)
GO

which is called like so:

DECLARE @Data [dbo].[MyType]

INSERT INTO @Data
EXEC [dbo].[sp_MyProc]

SET @Xml = (SELECT * FROM @Data FOR XML AUTO)
SELECT  @Xml

-- Get a table back
EXEC uSpShredUserDefinedTableType @Xml, '[dbo].[MyType]'
GO

However the SP I have written is returning NULL as the NODE name for the XML does not come back as @UserTableType:

CREATE PROC dbo.uSpShredUserDefinedTableType @Xml XML, @UserTableType SYSNAME AS

       DECLARE @Sql NVARCHAR(MAX)

       SELECT @Sql = 'SELECT ' +
           STUFF((SELECT '      ,T.Data.value(''@' +
                         c.name + ''', ''' +
                         t.name +
                         CASE WHEN c.user_type_id IN (165,167,173,175,231,239)
                              THEN '(' + CONVERT(VARCHAR, c.max_length) + ')'
                              WHEN c.user_type_id IN (106, 108)
                              THEN '(' + CONVERT(VARCHAR, c.precision)
                                   + ', ' + CONVERT(VARCHAR, c.scale) + ')'
                              ELSE '' END +
                         ''') AS ' + c.name + CHAR(10)
                  FROM   sys.columns c
                         INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
                  WHERE  object_id IN (SELECT type_table_object_id
                                       FROM sys.table_types
                                       WHERE name = @UserTableType)
                         AND t.name !='xml'
                  FOR XML PATH('')), 1, 7, '') +
           'FROM   @Xml.nodes(''/' + @UserTableType + ''') T(Data)'


    EXEC sp_executesql @Sql, N'@Xml XML', @Xml = @Xml
GO

So can anyone see where I am going wrong in my stored procedure above so that I can make it function the same way as Danny's example but for user defined table types?

[EDIT: FINAL SOLUTION]

I had to modify Devart's solution to:

• Correctly deal with "[" as the SQL being generated an error 'Syntax error near '[', expected a "node test".'
• Fix the generation of anything declared as (MAX) as that would generate NVARCHAR(-1)

Proc:

CREATE PROC [generator].[sp_ShredUserDefinedTableType] 
    @AXml XML, 
    @AUserTableType SYSNAME
AS
   DECLARE @Sql NVARCHAR(MAX)
   SELECT @Sql = 'SELECT ' +
        STUFF((SELECT '      ,T.Data.value(''@' +
                        c.name + ''', ''' +
                        t.name +
                        CASE WHEN c.user_type_id IN (165,167,173,175,231,239)
                             THEN '(' + CASE (c.max_length)
                                             WHEN -1 THEN 'MAX'
                                        ELSE
                                           CONVERT(VARCHAR, c.max_length) 
                                        END + ')'
                            WHEN c.user_type_id IN (106, 108)
                            THEN '(' + CONVERT(VARCHAR, c.precision) + ', ' + CONVERT(VARCHAR, c.scale) + ')'
                        ELSE 
                           '' 
                        END + ''') AS ' + c.name + CHAR(10)
        FROM sys.table_types tt 
        JOIN sys.columns c ON tt.type_table_object_id = c.[object_id]
        JOIN sys.types t ON c.user_type_id = t.user_type_id
        WHERE SCHEMA_NAME(tt.[schema_id]) + '.' + tt.name = @AUserTableType
            AND t.name != 'xml'
        FOR XML PATH('')), 1, 7, '') +
        'FROM   @Xml.nodes(''/' + @AUserTableType + ''') T(Data)'

    -- Print out the SQL that is necessary to query the XML that has been generated
    PRINT @sql
GO

It can be invoked as follows:

DECLARE @Xml XML
DECLARE @Data [dbo].[t_sp_Report_AR102_DataRetrieval_V1_1_Result]

INSERT INTO @Data
EXEC [dbo].[sp_Report_AR102_DataRetrieval_V1_1]
     @AOfficerParticipationNumberOfPriorDays = 30
    ,@AStartDate = '2013-04-01' 
    ,@AEndDate = '2013-06-30' 
    ,@AReferringServiceID = 4
    ,@AExcludePresentingIssueIDs = ''
    ,@AResultAsXML = @Xml OUTPUT


SET @Xml = (SELECT * FROM @Data FOR XML raw('dbo.t_sp_Report_AR102_DataRetrieval_V1_1_Result'))
EXEC [generator].[sp_ShredUserDefinedTableType] @Xml, 'dbo.t_sp_Report_AR102_DataRetrieval_V1_1_Result'

Answer 1

Try this one -

   DECLARE @Xml XML, @UserTableType SYSNAME = '[dbo].[MyType]'
   DECLARE @Sql NVARCHAR(MAX)
   SELECT @Sql = 'SELECT ' +
        STUFF((SELECT '      ,T.Data.value(''@' +
                        c.name + ''', ''' +
                        t.name +
                        CASE WHEN c.user_type_id IN (165,167,173,175,231,239)
                            THEN '(' + CONVERT(VARCHAR, c.max_length) + ')'
                            WHEN c.user_type_id IN (106, 108)
                            THEN '(' + CONVERT(VARCHAR, c.precision)
                                + ', ' + CONVERT(VARCHAR, c.scale) + ')'
                            ELSE '' END +
                        ''') AS ' + c.name + CHAR(10)
        FROM sys.table_types tt 
        JOIN sys.columns c ON tt.type_table_object_id = c.[object_id]
        JOIN sys.types t ON c.user_type_id = t.user_type_id
        -- your mistake: [dbo].[MyType] != MyType          
        WHERE '[' + SCHEMA_NAME(tt.[schema_id]) + '].[' + tt.name + ']' = @UserTableType
            AND t.name != 'xml'
        FOR XML PATH('')), 1, 7, '') +
        'FROM   @Xml.nodes(''/' + @UserTableType + ''') T(Data)'

PRINT @Sql

Output -

SELECT T.Data.value('@Id', 'int') AS Id
      ,T.Data.value('@MonthNumber', 'int') AS MonthNumber
      ,T.Data.value('@YearNumber', 'int') AS YearNumber
      ,T.Data.value('@NumberOfOfficersMakingReferrals', 'int') AS NumberOfOfficersMakingReferrals
      ,T.Data.value('@TierLevel', 'nvarchar(20)') AS TierLevel
      ,T.Data.value('@TierStrengthTotal', 'int') AS TierStrengthTotal
FROM   @Xml.nodes('/[dbo].[MyType]') T(Data)