I am having trouble inserting xml data into SQl server using the OPENXML. This is the SP I have. When I pass small XML content the Insert works but not when i have a bigger XML file.
ALTER PROC uspW3TeamList
@doc text
AS
BEGIN
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
INSERT INTO SportsSchedule (TeamName1,TeamName2,GameDate,GameStartTime,InsertedOn)
SELECT team_name1,team_name2,game_date,game_start,getDate()
FROM OPENXML (@idoc, '/SportsScheduleApi/SportType[type=*]/Schedule',2)
WITH (team_name1 text 'team_name1',team_name2 text 'team_name2',game_date datetime 'game_date',game_start datetime 'game_start')
EXEC sp_xml_removedocument @idoc
END
GO
Do I have any other options of inserting data. If so please show with an example.
Thanks.
OPENXML: Error inserting xml into SQL Server 2000
bkavanaugh
The error messg I recieve is
String or binary data would be truncated. When I provide a smaller length xml file as a string usually the insert works fine. But when provided with a larger XML file this is the error messg I recieve. I have tried changing the data type in the SP from varchar(7000) to text also but that didnt help.
Matt j Cimino Crawford
What is the error message :)
Sahil Malik [MVP C#]
Author: Pro ADO.NET 2.0
http://www.winsmarts.com
andig2
No Sahil I don't get an error when I do a select like the one below:
ALTER PROC uspW3TeamList
@doc text
AS
BEGIN
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT team_name1,team_name2,game_date,game_start,getDate()
FROM OPENXML (@idoc, '/SportsScheduleApi/SportType[type=*]/Schedule',2)
EXEC sp_xml_removedocument @idoc
END
GO
Gyða
Okay that helps. The truncation as specified is happening not in your input parameters, but in the table that you are attempting to do an insert into. Instead of an insert, just replace that with a select and see if you still get that error.
Sahil Malik [MVP C#]
Author: Pro ADO.NET 2.0
http://www.winsmarts.com
ssuggs
hey man that error also comes due to inadequate size of the column in which you r trying to insert the values. try the same thing after increasing the size of column in the related table.
tdanut
Ditto .. and that confirms my suspicion - it's your table, not the input parameter. You need to fix your table, or your query.
Sahil Malik [MVP C#]
Author: Pro ADO.NET 2.0
http://www.winsmarts.com