Geeks With Blogs

News


Google RankGoogle PR™ - Post your Page Rank with MyGooglePageRank.com



The content on this site represents my own personal opinions and thoughts at the time of posting, and does not reflect those of my employer's in any way.

Disclaimer:- All postings in this blog is provided "AS IS" with no warranties, and confers no rights.
Gaurav Taneja Great dreams... never even get out of the box. It takes an uncommon amount of guts to put your dreams on the line, to hold them up and say, "How good or how bad am I?" That's where courage comes in.

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

Posted on Tuesday, October 14, 2008 12:17 AM SQL SERVER | Back to top


Comments on this post: sp_xml_preparedocument

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Gaurav Taneja | Powered by: GeeksWithBlogs.net