How to create a Table using XML content - SQL Server

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