MST_XML_TO_TABLE

GO
/****** Object:  StoredProcedure [dbo].[MST_XML_TO_TABLE]    Script Date: 12/21/2015 13:01:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[MST_XML_TO_TABLE]
(
       @XML XML = ''
       ,@DataType varchar(100) = '' --Pass 'Columns' for columns ELSE DEFAULT TABLE WILL BE RETURN
       ,@ColumnString nvarchar(4000) = '' output
)
AS

BEGIN

 IF LOWER(@DataType) = 'columns' -- THIS WILL RETURN COLUMNS OF XML FILE
    BEGIN
        DECLARE @Columlist varchar(8000) = ''
       
        SELECT @Columlist = @Columlist + T.N.value('local-name(.)', 'sysname') + ','
        FROM @XML.nodes('/DocumentElement/BFormatFile[1]/*') as T(N);
       
        SELECT @ColumnString = SUBSTRING(@Columlist,0,LEN(@Columlist))
       
     END

 ELSE -- THIS WILL RETURN DATA OF XML IN TABULAR FORMAT
    BEGIN
            DECLARE @SQL NVARCHAR(MAX) = ''
            DECLARE @Col NVARCHAR(MAX) = ', T.N.value(''[COLNAME][1]'', ''varchar(100)'') as [COLNAME]'

            SELECT @SQL = @SQL + REPLACE(@Col, '[COLNAME]', T.N.value('local-name(.)', 'sysname'))
            FROM @XML.nodes('/DocumentElement/BFormatFile[1]/*') as T(N)
       
            SET @SQL = 'select '+ STUFF(@SQL, 1, 2, '')+' from @XML.nodes(''/DocumentElement/BFormatFile'') as T(N)'

            --select @SQL
            EXEC sp_executesql @SQL, N'@XML xml', @XML
    END
END