sql xml 转datatable 存储过程笔记

最新推荐文章于 2024-09-22 15:25:09 发布

木房尘埃

最新推荐文章于 2024-09-22 15:25:09 发布

阅读量506

点赞数

分类专栏： sql 文章标签： xml 存储 sql table insert

sql 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

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