sql xml 转datatable 存储过程 笔记

 


CREATE PROCEDURE P_GetTable_By_XML @XML XML
AS  
    SELECT  S.value('local-name(.)', 'NVARCHAR(20)') AS ColumnName
    INTO    #tColumns
    FROM    @XML.nodes('/Root/Schema/*') T ( S )

    DECLARE @strXML NVARCHAR(MAX) = 'DECLARE @XML XML = N'''
        + CAST(@XML AS NVARCHAR(MAX)) + ''''
  
    DECLARE @strTableSql NVARCHAR(MAX) = ' DECLARE @tResultTable Table ('
    DECLARE @strInsertSql NVARCHAR(MAX) = ' INSERT INTO @tResultTable ('
    DECLARE @strSelSql NVARCHAR(MAX) = ' select '
    DECLARE @strFromSql NVARCHAR(MAX) = ' FROM @XML.nodes(''/Root/Row'') T(S)'
    DECLARE @strSelectSql NVARCHAR(MAX) = 'SELECT * FROM @tResultTable'

    SELECT  @strTableSql = @strTableSql + '[' + a.ColumnName
            + '] NVARCHAR(max),' ,
            @strInsertSql = @strInsertSql + '[' + a.ColumnName + '],' ,
            @strSelSql = @strSelSql + 'S.value(' + '''' + '(' + a.ColumnName
            + ')[1]'' , ''NVARCHAR(max)''),'
    FROM    #tColumns a

    SET @strTableSql = LEFT(@strTableSql, LEN(@strTableSql) - 1) + ')'
    SET @strInsertSql = LEFT(@strInsertSql, LEN(@strInsertSql) - 1) + ')'
    SET @strSelSql = LEFT(@strSelSql, LEN(@strSelSql) - 1)

    EXEC(@strXML + @strTableSql + @strInsertSql + @strSelSql + @strFromSql + @strSelectSql)


    DROP TABLE #tColumns
GO