Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Transact-SQL (2008)  problem with importing data from huge xml file

Author  Topic  Nader Starting Member 41 Posts Posted - 2010-05-10 : 19:14:44 Hi, I need help here is my code it works fine if the xml file is not large. It does not work if the xml file is large. here is my code thanks in advance -------------------------------- USE [SP3] GO /****** Object: StoredProcedure [dbo].[usp_user_longimportInfo_FileName_trial1] Script Date: 05/10/2010 16:09:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ---- Good One ---- for content ALTER procedure [dbo].[usp_user_longimportInfo_FileName_trial1] (@XMLFileName as nvarchar(100)) as begin set @XMLFileName='c:\trial\shortperson.xml' create table #tempPerson ([UserID] [int] IDENTITY(1,1) NOT NULL, [Email] [nvarchar](50) NULL, [Pass] [nvarchar](20) NULL, [FirstName] [nvarchar](20) NULL, [LastName] [nvarchar](40) NULL, [DisplayName] [nvarchar](50) NULL, [Profile] [nvarchar](max) NULL, [DisplayEmail] [nvarchar](50) NULL, [CellPhone] [nvarchar](20) NULL, [UpdatedBy] [int] NULL, [UpdateDate] [datetime] NULL, [Deleted] [bit] NULL) DECLARE @TempCurrentTime datetime DECLARE @USERID int Declare @UpdateDate datetime Declare @Email nvarchar(50) DECLARE @Pass NVARCHAR(20) DECLARE @FirstName NVARCHAR(20) DECLARE @LastName nvarchar(40) Declare @DisplayName nvarchar(50) Declare @Organization int Declare @UserAccessRole int Declare @OrgUserID nchar(12) Declare @OrgPassword nvarchar(50) Declare @OrgUserName nvarchar(50) declare @mySQL nvarchar(max) declare @PersonXML xml declare @ParamDefinition nvarchar(500) SELECT @TempCurrentTime = GETDATE() select @UpdateDate=@TempCurrentTime -- retrieve the file content as xml set @mysql=N'select @PersonXML= CONVERT(xml, BulkColumn, 2) FROM OPENROWSET(Bulk '''+ @XMLFileName+''', SINGLE_BLOB ) [rowsetresults]' Set @ParamDefinition = '@XMLFileName nvarchar(max),@PersonXML XML out' Execute sp_Executesql @mySql, @ParamDefinition, @XMLFileName, @PersonXML out insert into #tempPerson (Email,Pass,FirstName,LastName,DisplayName,[Profile],DisplayEmail,CellPhone,UpdatedBy,UpdateDate,deleted) (SELECT TempXML.Node.value('(email)[1]','nvarchar(50)') as Email, ' ', TempXML.Node.value('(name/n/given)[1]', 'nVARCHAR(50)') as FirstName, TempXML.Node.value('(name/n/family)[1]', 'VARCHAR(50)') as LastName, TempXML.Node.value('(name/fn)[1]','nvarchar(50)') as DisplayName, ' ', TempXML.Node.value('(email)[1]','nvarchar(50)') as DisplayEmail, ' ',1,GETDATE(),0 FROM @PersonXML.nodes('/enterprise/person') TempXML (Node)) select * from #tempPerson insert into dbo.longPersonError select USERID, Email from( MERGE SP.UserTrial USING (SELECT Email,Pass,FirstName,LastName,DisplayName,[Profile],DisplayEmail,CellPhone,UpdatedBy,UpdateDate,deleted from #tempPerson) AS source ON (SP.UserTrial.Email = source.Email) WHEN Not MATCHED THEN INSERT (Email,Pass,FirstName,LastName,DisplayName,[Profile],DisplayEmail,CellPhone,UpdatedBy,UpdateDate,deleted) VALUES (source.Email,source.Pass,source.FirstName,source.LastName,source.DisplayName,source.[Profile],source.DisplayEmail,source.CellPhone,source.UpdatedBy,source.UpdateDate,source.deleted) when matched then UPDATE SET SP.UserTrial.Email = SP.UserTrial.Email OUTPUT $ACTION, deleted.userid,deleted.Email ) AS CHANGES (Action,userid,email) WHERE Action = 'UPDATE'; end -------------------------------