Problem inserting xml string into temp table (inserting only a part of string not complete)
-
hi Can anyone resolve my problem> I create a temp table and inserts data into it through Xml String , spliting it by the character '|' . The data is inserted into the table fine but for last row it inserts only a part of it and does not inserts (" Remarks="">|) of the xml string. Can anyo please help or modify...the input xml string will be as given below. {TO KNOW THE PROBLEM PRACTICALLY COPY THIS CODE IN NEW QUERY EDITOR AND EXECUTE} --Declaring a temporary @TempTable declare @TempTable table ( AutoId int identity, items varchar(max) ) --Decaring a Variable Declare @ExcelDataInXml varchar(max) Set @ExcelDataInXml='||||||||
-
hi Can anyone resolve my problem> I create a temp table and inserts data into it through Xml String , spliting it by the character '|' . The data is inserted into the table fine but for last row it inserts only a part of it and does not inserts (" Remarks="">|) of the xml string. Can anyo please help or modify...the input xml string will be as given below. {TO KNOW THE PROBLEM PRACTICALLY COPY THIS CODE IN NEW QUERY EDITOR AND EXECUTE} --Declaring a temporary @TempTable declare @TempTable table ( AutoId int identity, items varchar(max) ) --Decaring a Variable Declare @ExcelDataInXml varchar(max) Set @ExcelDataInXml='||||||||
sandeep kumar pundhir wrote:
insert into @TempTable select * from SPLIT(@ExcelDataInXml,'|')
I assume that SPLIT() is a UDF? What does that function look like?
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
sandeep kumar pundhir wrote:
insert into @TempTable select * from SPLIT(@ExcelDataInXml,'|')
I assume that SPLIT() is a UDF? What does that function look like?
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
Yes SPLIT is a User Defined Function set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1)) RETURNS @Results TABLE (Items nvarchar(4000)) AS BEGIN DECLARE @INDEX INT DECLARE @SLICE nvarchar(4000) -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z -- ERO FIRST TIME IN LOOP SELECT @INDEX = 1 -- following line added 10/06/04 as null -- values cause issues IF @String IS NULL RETURN WHILE @INDEX !=0 BEGIN -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER SELECT @INDEX = CHARINDEX(@Delimiter,@STRING) -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE IF @INDEX !=0 SELECT @SLICE = LEFT(@STRING,@INDEX - 1) ELSE SELECT @SLICE = @STRING -- PUT THE ITEM INTO THE RESULTS SET INSERT INTO @Results(Items) VALUES(@SLICE) -- CHOP THE ITEM REMOVED OFF THE MAIN STRING SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX) -- BREAK OUT IF WE ARE DONE IF LEN(@STRING) = 0 BREAK END RETURN END
-
Yes SPLIT is a User Defined Function set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1)) RETURNS @Results TABLE (Items nvarchar(4000)) AS BEGIN DECLARE @INDEX INT DECLARE @SLICE nvarchar(4000) -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z -- ERO FIRST TIME IN LOOP SELECT @INDEX = 1 -- following line added 10/06/04 as null -- values cause issues IF @String IS NULL RETURN WHILE @INDEX !=0 BEGIN -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER SELECT @INDEX = CHARINDEX(@Delimiter,@STRING) -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE IF @INDEX !=0 SELECT @SLICE = LEFT(@STRING,@INDEX - 1) ELSE SELECT @SLICE = @STRING -- PUT THE ITEM INTO THE RESULTS SET INSERT INTO @Results(Items) VALUES(@SLICE) -- CHOP THE ITEM REMOVED OFF THE MAIN STRING SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX) -- BREAK OUT IF WE ARE DONE IF LEN(@STRING) = 0 BREAK END RETURN END
Your @ExcelDataInXml string is longer than 4000 characters, and your Split function only accepts 4000 characters.
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters