Scripts to insert and update rows with XML

USE [pubs]
GO
/****** Object: UserDefinedFunction [dbo].[fnGetTableSchemaSelectInto] Script Date: 09/22/2010 09:57:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: WA
-- Create date: 03/02/2010
-- Description: Retrieve Table Schema for Select Into
-- =============================================
CREATE FUNCTION [dbo].[fnGetTableSchemaSelectInto]
(
@TableName varchar(50)
)
RETURNS varchar(2000)
AS
BEGIN

DECLARE @ResultVar varchar(2000)

DECLARE @i int, @sSql varchar(2000)

SELECT @sSql = '' 

SELECT @i = MIN(ordinal_Position)
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName 
AND Column_Name not in 
(SELECT c.name AS ColumnName
FROM sys.columns AS c INNER JOIN sys.tables AS t ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1 and t.name = @TableName)
AND data_Type <> 'timestamp' 

WHILE @i is not null
BEGIN 

SELECT @sSql = @sSql + replace(replace(column_name,' ',''),'/','') + ','
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName 
AND ordinal_Position = @i
AND data_Type <> 'timestamp'

SELECT @i = min(ordinal_Position) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName 
AND Column_Name not in 
(SELECT c.name AS ColumnName
FROM sys.columns AS c INNER JOIN sys.tables AS t ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1 and t.name = @TableName) 
AND data_Type <> 'timestamp' 
AND ordinal_Position > @i
END

SET @sSql = @sSql + '//'

SET @ResultVar = replace(@sSql, ',//','')

-- Return the result of the function
RETURN @ResultVar

END
GO
/****** Object: UserDefinedFunction [dbo].[fnGetTableSchemaInsert] Script Date: 09/22/2010 09:57:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: WA
-- Create date: 12/28/2009
-- Description: Retrieve Table Schema for Insert
-- =============================================
CREATE FUNCTION [dbo].[fnGetTableSchemaInsert]
(
@TableName varchar(50)
)
RETURNS varchar(2000)
AS
BEGIN

DECLARE @ResultVar varchar(2000)

DECLARE @i int, @sSql varchar(2000)

SELECT @sSql = '' 

SELECT @i = MIN(ordinal_Position)
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName 
AND Column_Name not in 
(SELECT c.name AS ColumnName
FROM sys.columns AS c INNER JOIN sys.tables AS t ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1 and t.name = @TableName)
AND data_Type <> 'timestamp' 

WHILE @i is not null
BEGIN 

SELECT @sSql = @sSql + CASE data_type
WHEN 'varchar' THEN 
CASE WHEN character_maximum_length = -1 THEN
'[' + replace(replace(column_name,' ',''),'/','') + '] ' + data_type + '(MAX), '
ELSE 
'[' + replace(replace(column_name,' ',''),'/','') + '] ' + data_type + '(' + cast(isnull(character_maximum_length,numeric_precision) as varchar(60)) + '), '
END
WHEN 'nvarchar' THEN '[' + replace(replace(column_name,' ',''),'/','') + '] ' + data_type + '(' + cast(isnull(character_maximum_length,numeric_precision) as varchar(60)) + '), '
WHEN 'char' THEN '[' + replace(replace(column_name,' ',''),'/','') + '] ' + data_type + '(' + cast(isnull(character_maximum_length,numeric_precision) as varchar(60)) + '), '
ELSE '[' + replace(replace(column_name,' ',''),'/','') + '] ' + data_type + ', '
END
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName 
AND ordinal_Position = @i
AND data_Type <> 'timestamp'

SELECT @i = min(ordinal_Position) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName 
AND Column_Name not in 
(SELECT c.name AS ColumnName
FROM sys.columns AS c INNER JOIN sys.tables AS t ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1 and t.name = @TableName) 
AND data_Type <> 'timestamp' 
AND ordinal_Position > @i
END

SET @sSql = @sSql + '//'

SET @ResultVar = replace(@sSql, ', //','')

-- Return the result of the function
RETURN @ResultVar

END
GO
/****** Object: UserDefinedFunction [dbo].[fnGetTableSchema] Script Date: 09/22/2010 09:57:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: WA
-- Create date: 12/28/2009
-- Description: Retrieve Table Schema
-- =============================================
CREATE FUNCTION [dbo].[fnGetTableSchema]
(
@TableName varchar(50)
)
RETURNS varchar(2000)
AS
BEGIN

DECLARE @ResultVar varchar(2000)

DECLARE @i int, @sSql varchar(2000)

SELECT @sSql = '' 

SELECT @i = MIN(ordinal_Position)
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName
AND data_Type <> 'timestamp' 

WHILE @i is not null
BEGIN 

SELECT @sSql = @sSql + CASE data_type
WHEN 'varchar' THEN 
CASE WHEN character_maximum_length = -1 THEN
'[' + replace(replace(column_name,' ',''),'/','') + '] ' + data_type + '(MAX), '
ELSE 
'[' + replace(replace(column_name,' ',''),'/','') + '] ' + data_type + '(' + cast(isnull(character_maximum_length,numeric_precision) as varchar(60)) + '), '
END
WHEN 'nvarchar' THEN '[' + replace(replace(column_name,' ',''),'/','') + '] ' + data_type + '(' + cast(isnull(character_maximum_length,numeric_precision) as varchar(60)) + '), '
WHEN 'char' THEN '[' + replace(replace(column_name,' ',''),'/','') + '] ' + data_type + '(' + cast(isnull(character_maximum_length,numeric_precision) as varchar(60)) + '), '
ELSE '[' + replace(replace(column_name,' ',''),'/','') + '] ' + data_type + ', '
END
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName
AND ordinal_Position = @i

SELECT @i = min(ordinal_Position) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName
AND data_Type <> 'timestamp'
AND ordinal_Position > @i
END

SET @sSql = @sSql + '//'

SET @ResultVar = replace(@sSql, ', //','')

-- Return the result of the function
RETURN @ResultVar

END
GO
/****** Object: UserDefinedFunction [dbo].[fnGetTableKeys] Script Date: 09/22/2010 09:57:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: WA
-- Create date: 12/28/2009
-- Description: Retrieve Table Primary Keys
-- =============================================
CREATE FUNCTION [dbo].[fnGetTableKeys] 
( 
@TableName varchar(50)
)
RETURNS TABLE 
AS
RETURN 
(
SELECT Ordinal_position, Column_Name 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE table_name = @TableName
)
GO
/****** Object: UserDefinedFunction [dbo].[fnGetPrimaryKeys] Script Date: 09/22/2010 09:57:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: WA
-- Create date: 12/28/2009
-- Description: Retrieve Table Primary Keys
-- =============================================
CREATE FUNCTION [dbo].[fnGetPrimaryKeys]
(
@TableName varchar(50)
)
RETURNS varchar(2000)
AS
BEGIN

DECLARE @ResultVar varchar(2000)

DECLARE @i int, @sSql varchar(2000)

SELECT @i = 1, @sSql = '' 

WHILE @i is not null
BEGIN 

SELECT @sSql = @sSql + @TableName + '.' + Column_Name + '=xm.' + Column_Name + ' AND '
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE table_name = @TableName
AND Ordinal_position = @i

-- MoveNext
SELECT @i = min(Ordinal_position) 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE table_name = @TableName
AND Ordinal_position > @i
END

SET @sSql = @sSql + '//'

SET @ResultVar = replace(@sSql, 'AND //','')

-- Return the result of the function
RETURN @ResultVar

END
GO
/****** Object: UserDefinedFunction [dbo].[fnSetTableSchemaSelect] Script Date: 09/22/2010 09:57:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: WA
-- Create date: 12/28/2009
-- Description: Retreive Table Schema for Insert
-- =============================================
CREATE FUNCTION [dbo].[fnSetTableSchemaSelect]
(
@TableName varchar(50)
)
RETURNS varchar(2000)
AS
BEGIN

DECLARE @ResultVar varchar(2000)

DECLARE @i int, @sSql varchar(2000)

SELECT @sSql = '' 

SELECT @i = MIN(ordinal_Position)
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName 
AND Column_Name not in 
(SELECT c.name AS ColumnName
FROM sys.columns AS c INNER JOIN sys.tables AS t ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1 and t.name = @TableName)
AND data_Type <> 'timestamp' 

WHILE @i is not null
BEGIN 

SELECT @sSql = @sSql + CASE data_type
WHEN 'datetime' THEN 
'[' + replace(replace([column_name],' ',''),'/','') + ']= CASE WHEN dbo.fnIsDate([' + replace(replace([column_name],' ',''),'/','') + '])=1 THEN NULL ELSE [' + replace(replace([column_name],' ',''),'/','') + '] END, ' 
ELSE '[' + replace(replace(column_name,' ',''),'/','') + '], '
END
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName
AND ordinal_Position = @i
AND data_Type <> 'timestamp'

SELECT @i = min(ordinal_Position) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName
AND Column_Name not in 
(SELECT c.name AS ColumnName
FROM sys.columns AS c INNER JOIN sys.tables AS t ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1 and t.name = @TableName) 
AND data_Type <> 'timestamp' 
AND ordinal_Position > @i
END

SET @sSql = @sSql + '//'

SET @ResultVar = replace(@sSql, ', //','')

-- Return the result of the function
RETURN @ResultVar

END
GO
/****** Object: UserDefinedFunction [dbo].[fnIsDate] Script Date: 09/22/2010 09:57:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: WA
-- Create date: 01/22/2010
-- Description: Validate good Date
-- =============================================
CREATE FUNCTION [dbo].[fnIsDate] 
(
@DateTime datetime
)
RETURNS bit
AS
BEGIN

DECLARE @ResultVar bit
SET @ResultVar = 0

IF @DateTime = '1900-01-01 00:00:00.000'
SET @ResultVar = 1

-- Return the result of the function
RETURN @ResultVar

END
GO
/****** Object: UserDefinedFunction [dbo].[fnGetTableUpdate] Script Date: 09/22/2010 09:57:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: WA
-- Create date: 12/28/2009
-- Description: Retrieve Table Update
-- =============================================
CREATE FUNCTION [dbo].[fnGetTableUpdate]
(
@TableName varchar(50)
)
RETURNS varchar(4000)
AS
BEGIN

DECLARE @ResultVar varchar(4000)

DECLARE @i int, @sSql varchar(4000)

SELECT @sSql = '' 

SELECT @i = MIN(ordinal_Position)
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName
AND ordinal_Position not in (SELECT Ordinal_Position FROM [dbo].[fnGetTableKeys](@TableName)) 
AND [Column_Name]<> 'SysTimeStamp' 

WHILE @i is not null
BEGIN 

SELECT @sSql = @sSql + CASE data_type
WHEN 'datetime' THEN 
'[' + replace(replace([column_name],' ',''),'/','') + ']= CASE WHEN dbo.fnIsDate(xm.[' + replace(replace([column_name],' ',''),'/','') + '])=1 THEN NULL ELSE xm.[' + replace(replace([column_name],' ',''),'/','') + '] END, ' 
ELSE '[' + replace(replace(column_name,' ',''),'/','') + '] = xm.' + replace(replace(column_name,' ',''),'/','') + ', '
END
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName
AND [Column_Name]<> 'SysTimeStamp' 
AND ordinal_Position = @i

SELECT @i = min(ordinal_Position) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE [TABLE_NAME] = @TableName
AND ordinal_Position not in (SELECT Ordinal_Position FROM [dbo].[fnGetTableKeys](@TableName))
AND [Column_Name]<> 'SysTimeStamp' 
AND ordinal_Position > @i
END

SET @sSql = @sSql + '//'

SET @ResultVar = replace(@sSql, ', //','')

-- Return the result of the function
RETURN @ResultVar

END
GO
/****** Object: StoredProcedure [dbo].[prXMLDataInsert] Script Date: 09/22/2010 09:57:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: WA
-- Create date: 12/24/2009
-- Description: Translate XML to SQL RecordSet for Insert
-- =============================================
CREATE PROCEDURE [dbo].[prXMLDataInsert] 
(
@XmlData xml
)
AS
BEGIN

SET NOCOUNT ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

DECLARE @hdoc int

-- Prepare XML document
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlData

-- Set Raw XML Schema
SELECT *
INTO #xmlDoc 
FROM OPENXML( @hdoc, '//*',2)

-- Set Primary Table to use
SELECT DISTINCT Identity(int,1,1) id, rt.localname + '/' + tbl.localname + '/' + col.localname as NodePath, tbl.localname as NodeRow
INTO #xml
FROM #xmlDoc rt 
INNER JOIN #xmlDoc tbl
ON rt.id = tbl.parentID and rt.parentID is null
INNER JOIN #xmlDoc col
ON tbl.id = col.parentID

DECLARE @i int, @NodePath varchar(255), @NodeRow varchar(50), 
@NodeKeys varchar(255), @NodeCol varchar(2000), @UpdateNodes varchar(2000), 
@sSql nvarchar(4000), @SetSchemaSelect varchar(4000), @iVars varchar(2000)

-- Set id of first row
SELECT @i = min(id) from #xml 

-- Begin looping through xml recordset
WHILE @i is not null
BEGIN 
SELECT @NodePath = NodePath, @NodeRow = NodeRow FROM #xml WHERE id = @i 

-- Get Table Schema for XML data columns
SELECT @NodeCol =[dbo].[fnGetTableSchemaInsert](@NodeRow)
SELECT @SetSchemaSelect = [dbo].[fnSetTableSchemaSelect](@NodeRow)
SELECT @ivars = [dbo].[fnGetTableSchemaSelectInto](@NodeRow)

DECLARE @param NVARCHAR(50), @pkID int, @pkIDOUT int

SET @param = N'@hdoc INT, @pkIDOUT INT OUTPUT'

/******* This updates xml Recordset on primary keys of a given table *******/

SET @sSql = 'INSERT INTO ' + @NodeRow + '(' + @iVars + ') SELECT ' + @SetSchemaSelect + ' FROM OPENXML( @hdoc, ''' + @NodePath + ''',2) WITH (' + @NodeCol + ') as xm SELECT @pkIDOUT = SCOPE_IDENTITY()'

/******* Execute the query and pass in the @hdoc for update *******/
EXEC sp_executesql @sSql, @param, @hdoc, @pkIDOUT=@pkID OUTPUT

/***** Movenext *****/
SELECT @i = min(id) FROM #xml WHERE id > @i
END

-- Release @hdoc
EXEC sp_xml_removedocument @hdoc
DROP TABLE #xmlDoc
DROP TABLE #xml

END
GO
/****** Object: StoredProcedure [dbo].[prXMLDataUpdate] Script Date: 09/22/2010 09:57:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: WA
-- Create date: 12/24/2009
-- Description: Translate XML to SQL RecordSet for Update
-- =============================================
CREATE PROCEDURE [dbo].[prXMLDataUpdate] 
(
@XmlData xml
)
AS
BEGIN

SET NOCOUNT ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

DECLARE @hdoc int

-- Prepare XML document
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlData

-- Set Raw XML Schema
SELECT *
INTO #xmlDoc 
FROM OPENXML( @hdoc, '//*',2)

-- Set Primary Table to use
SELECT DISTINCT Identity(int,1,1) id, rt.localname + '/' + tbl.localname + '/' + col.localname as NodePath, tbl.localname as NodeRow
INTO #xml
FROM #xmlDoc rt 
INNER JOIN #xmlDoc tbl
ON rt.id = tbl.parentID and rt.parentID is null
INNER JOIN #xmlDoc col
ON tbl.id = col.parentID

DECLARE @i int, @NodePath varchar(255), @NodeRow varchar(50), @NodeKeys varchar(255), @NodeCol varchar(4000), @UpdateNodes varchar(4000), @sSql nvarchar(4000)

-- Set id of first row
SELECT @i = min(id) from #xml 

-- Begin looping through xml recordset
WHILE @i is not null
BEGIN 
SELECT @NodePath = NodePath, @NodeRow = NodeRow FROM #xml WHERE id = @i 

-- Get Table Schema for XML data columns
SELECT @NodeCol = [dbo].[fnGetTableSchema](@NodeRow)--:00
SELECT @UpdateNodes =[dbo].[fnGetTableUpdate](@NodeRow)--:00
SELECT @NodeKeys = [dbo].[fnGetPrimaryKeys](@NodeRow)--:00

DECLARE @param NVARCHAR(50)
SET @param = N'@hdoc INT'

/******* This updates xml Recordset on primary keys of a given table *******/
SET @sSql = 'UPDATE ' + @NodeRow + ' SET ' + @UpdateNodes + ' FROM OPENXML( @hdoc, ''' + @NodePath + ''',2) WITH (' + @NodeCol + ') as xm INNER JOIN ' + @NodeRow + ' ON ' + @NodeKeys

/******* Execute the query and pass in the @hdoc for update *******/
EXEC sp_executesql @sSql, @param, @hdoc

/***** Movenext *****/
SELECT @i = min(id) FROM #xml WHERE id > @i
END

-- Release @hdoc
EXEC sp_xml_removedocument @hdoc
DROP TABLE #xmlDoc
DROP TABLE #xml

END
GO