How to create a Table using XML content - SQL Server

PandianS
Posted by PandianS under Sql Server category on | Points: 40 | Views : 6092
1. Create a stored procedure:
IF OBJECT_ID('USP_CreateTable','P') IS NOT NULL
DROP PROC USP_CreateTable
GO
CREATE PROC USP_CreateTable
(
@XMLNodes NVARCHAR(MAX)
)AS
BEGIN
SET NOCOUNT ON

DECLARE
@xmldoc INT ,
@SQL NVARCHAR(MAX) ,
@TableName NVARCHAR(100) ,
@AlreadyExists NVARCHAR(100)


EXEC sp_xml_preparedocument @xmldoc OUTPUT, @xmlNodes

SELECT @TableName = TableName FROM OPENXML(@xmldoc, N'/Table') WITH (TableName VARCHAR(100) './@Name')

SELECT @SQL = COALESCE(@SQL,'') + Tablename + CHAR(9) + ColumnType + CASE WHEN ColumnSize >0 THEN '(' + CAST(ColumnSize AS VARCHAR)+ '),' ELSE ',' END + CHAR(10) FROM OPENXML(@xmldoc, N'/Table/Column') WITH (TableName VARCHAR(100) './@Name', ColumnType VARCHAR(20) './@Type', ColumnSize INT './@Size')

EXEC sp_xml_removedocument @xmldoc

SELECT @AlreadyExists = 'IF OBJECT_ID('''+ @TableName + ''') IS NULL' + CHAR(10)

SELECT @SQL = @AlreadyExists + 'CREATE TABLE ' + @TableName + CHAR(10) + '(' + CHAR(10) + LEFT(@SQL,LEN(@SQL)-2) + CHAR(10) + ')'

EXEC(@SQL)
END
GO
2. Pass the XML content to Stored procedure:
DECLARE @xmlNodes		NVARCHAR(MAX)

SELECT @xmlNodes ='<Table Name="Tb_DotnetFundaTable">
<Column Name="ID" Type="INT" Size="" />
<Column Name="Column1" Type="VARCHAR" Size="50" />
<Column Name="Column2" Type="DATETIME" Size="" />
</Table>'

EXEC USP_CreateTable @xmlNodes
GO
Note: You can change the Stored procedure to implement the additional requirements/properies like IDENTITY, Floating points, Constraints, Exception handling,...Etc for Tables accordingly...

www.sqlserverbuddy.blogspot.com

Comments or Responses

Login to post response