Play Game With SQL Server Using XML

Insert and Update of table in SQL Server using XMl Parameter

CREATE PROCEDURE GeneralSave
@Params XML
AS
BEGIN
SET NOCOUNT ON;


IF EXISTS(SELECT *FROM WHERE =(SELECT @Params.value('(//ParameterName)[1]','VARCHAR(10)')))
BEGIN
UPDATE TesClients
SET Description=(SELECT @Params.value('(//ParameterName)[1]','VARCHAR(30)'))
WHERE ClientID=(SELECT @Params.value('(//ParameterName)[1]','VARCHAR(10)'))
END
ELSE
BEGIN
INSERT INTO SELECT (SELECT @Params.value('(//ParameterName)[1]','VARCHAR(30)')),
(SELECT @Params.value('(//ParameterName)[1]','VARCHAR(30)')),
'700N','CHANDRU@F.COM',0,GETDATE(),'HIBARE'
END

END
GO


DECLARE @x XML
set @x='2BIKE
Conciliación Automáticasshiku0'
EXECUTE GeneralSave @x


Massive Updation of table in SQL Server Using Temp Table and XML

CREATE PROCEDURE GeneralSaveFunctiona
@ParArray NVARCHAR(MAX),
@TableName VARCHAR(MAX),
@KeyColumn1 VARCHAR(MAX),
@KeyColumn2 VARCHAR(MAX) = '',
@KeyColumn3 VARCHAR(MAX) = ''
AS
BEGIN
SET NOCOUNT ON;
Declare @SQL NVARCHAR(MAX)

SET @SQL = N'
IF EXISTS(SELECT * FROM sysobjects WHERE type = ''u'' and name = ''#tmp'')
BEGIN
DROP TABLE #tmp
END

DECLARE @handle int
EXEC sp_xml_preparedocument @handle OUTPUT, N''' + @ParArray + '''
SELECT * INTO #tmp FROM OPENXML (@handle, ''//item'',2) WITH ' + @TableName + '
EXEC sp_xml_removedocument @handle

Declare @Column varchar(100),@Command Nvarchar(max)
set @Command = N''Update TesActions
SET''

Declare myCursor Cursor for
select Column_Name from information_schema.columns where Table_Name = ''' + @TableName + '''
OPEN myCursor
FETCH NEXT FROM myCursor INTO @Column
WHILE @@FETCH_STATUS = 0
BEGIN
set @Command = @Command + ''
'' + @Column + '' = #tmp.'' + @Column + '' , ''

FETCH NEXT FROM myCursor INTO @Column
END
CLOSE myCursor
DEALLOCATE myCursor
select @Command = substring(@Command,0,Len(@Command)-1)
set @Command = @Command + ''
'' + ''FROM #tmp
WHERE ' + @TableName + '.' + @KeyColumn1 + ' = #TMP.' + @KeyColumn1 + ''

if (@KeyColumn2 <> '')
begin
set @SQL = @SQL + ' AND
' + @TableName + '.' + @KeyColumn2 + ' = #TMP.' + @KeyColumn2
end

if (@KeyColumn3 <> '')
begin
set @SQL = @SQL + ' AND
' + @TableName + '.' + @KeyColumn3 + ' = #TMP.' + @KeyColumn3
end

set @SQL = @SQL + '''
exec SP_executesql @Command
Drop Table #tmp'

print @SQL
exec sp_executesql @SQL

END
GO


DECLARE @x varchar(max)
select @x= 'CONCILAUTOBIKEConciliación Automáticasshiku0CONCILINTEGINTEGIntegración en tesorería0CONCILMANMANConciliación Manual0'


EXECUTE GeneralSaveFunctiona @x,'',''

Replace Special Character for the string provided

--Returns special Character for the whole string

SELECT REPLICATE(‘*’,LEN(‘AnyString’))

Output: ********