Tuesday, October 11, 2011

Inserting Data with xml in sql server

DataSet dsCategories = new DataSet();
 dsCategories = objCompany.GetDefaultData();
 string strCategoryDetails = dsCategories.GetXml();



Create PROCEDURE [dbo].[usp_Testingxml]             
 (                          
  @CategoryDetails nText  
 )                          
                           
AS                          
                          
DECLARE @handle int   
Declare @CompanyId int                          
set @CompanyId = 1                         
   
 
  EXEC sp_xml_preparedocument @handle OUTPUT, @CategoryDetails                      
  Insert into tblCmpcategories(CategoryId,CategoryName,CompanyId)
  SELECT  CategoryId, CatName, @CompanyId FROM                          
  OPENXML (@handle, '/NewDataSet/Table',2)  
  WITH (CategoryId int ,CatName VARCHAR(50) ) --xml fields                         
  EXEC sp_xml_removedocument @handle

--http://msdn.microsoft.com/en-us/magazine/cc163782.aspx

No comments:

Post a Comment