Example displaying when data is sent in form of xml document
CREATE PROCEDURE XMLINSERT
@intShowVersion INT =0,
@strClassName varchar(250),
@strLang varchar(10),
@strXml text='',
@intError int output
AS
SET NOCOUNT ON
/* @intShowVersion INTEGER =0,
@strCreatedBy VARCHAR(20) = '' ,
@intTemplateID INTEGER = 0,
@dtClientCreatedOn DATETIME = '' ,
@strUpdatedBy VARCHAR(20) = '' ,
@dtClientUpdatedOn DATETIME = '' ,
@strXml text='',
SET DATEFORMAT dmy
IF (@intShowVersion = 1 )
BEGIN
SELECT VERSION='$Revision: 1 $'
RETURN 0
END
ELSE
BEGIN
BEGIN TRAN --Transaction begins
-- DECLARE @intError INTEGER
DECLARE @strDisplayref VARCHAR(200)
DECLARE @intTableId varchar(10)
Declare @StrQuery nvarchar(Max)
Declare @strDelQuery nvarchar(Max)
DECLARE @hDoc INT
Declare @strTableName nvarchar(100)
/*=============================================== START: INSERT INTO [*.Multilingual]==================================================*/
EXEC sp_xml_preparedocument @hDoc OUTPUT,@strXml
set @strDisplayref=(select Top 1 CPM.CM_DisplayReference_C from SF_ClassProperty_Master CPM
where CPM.CM_ClassIndexId_I =(Select CM.CM_ClassIndexId_I from Sf_Class_Master CM where CM.CM_ClassNAme_C=@strClassName) and CPM.CM_DisplayReference_C is not null )
set @strDisplayref= Substring(@strDisplayref,2,Len(@strDisplayref))
print @strDisplayref
set @intTableId = (Select CM_TableName_C from sf_class_master where CM_ClassNAme_C=@strClassName)
print @intTableId
set @strDisplayref = 'Rowid,[Language],'+ @strDisplayref
print @strXml
set @strTableName = ''+ @intTableId +'.Multilingual'
print @strTableName
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype='u' AND name=''+@strTableName+'')
Begin
set @strDelQuery =N'Delete from ['+ @intTableId +'.Multilingual] where [Language]='''+@strLang+''''
print @strDelQuery
exec sp_executesql @strDelQuery,N'@intTableId varchar,@strLang nvarchar(10)',@intTableId,@strLang
set @strQuery=N' INSERT INTO ['+ @intTableId +'.Multilingual] ('+ @strDisplayref+' )SELECT Rowid,Lang,DisplayRef FROM OPENXML(@hdoc, ''//*/RefValue'',1) WITH(Rowid Int,Lang nvarchar(10),DisplayRef nvarchar(500)) '
print @strquery
exec sp_executesql @strQuery,N'@hdoc int', @hdoc
/*=============================================== END: INSERT INTO [*.Multilingual]==================================================*/
EXEC sp_xml_removedocument @hDoc
End
/*=============================================== END: ==================================================*/
SELECT @intError = @@error
print @intError
IF @intError = 0
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END
END
SET NOCOUNT OFF