Hi, Please see my code below it works fine with small xml files but it does not work with large xml files (of size 2496kb or more)
appreciate your help
-------------------
USE [SP3]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
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
Thanks in advance