store XML string in DB

[ktucci]

how can i best store the XML string resulting from the query below...i presume inserting into an 'image' field would be the answer assuming these XML string will be very large...i was wondering if anyone has any way i can accomplish this inserting of the XML string into an 'image' field through SQL and not having to use VB or ASP like i have been doing for inserting files into an 'image' field in the past

select
account_id,
account_number,
opened_datetime

from account

for xml auto

----------------------
thanks in advance

keith

 

[RobHudson]

Hi Keith

I wrote this SP sometime ago when I was trying to work out how to load an image into a binary field. NOt exactly what you want, but might help point in the right direction...

/*Inserts a file into a binary field*/
/*Uses the command prompt [of all things!] to do it*/
CREATE PROCEDURE sp_TextCopy
(
	@ServerName varchar(30),
	@DB varchar(30),
	@TableName varchar(30),
	@ColName varchar(30),
	@FileName varchar(30),		/*C:\pic.gif*/
	@WhereClause varchar(30),	/*WHERE IDField = 1*/
	@Direction varchar(30)		/*I: for in; O: for out*/
)

AS

/*Handle the path to use to get to the textcopy function*/
DECLARE @AppPath varchar(100)
IF @ServerName = '(local)'
	SELECT @AppPath = 'C:\PROGRA~1\MICROS~4\MSSQL\BINN\'
ELSE
	SELECT @AppPath = 'C:\MSSQL7\BINN\'

/*Build the command prompt action to load the file*/
DECLARE @Exec varchar(1000)
SELECT @Exec = @AppPath + 'TEXTCOPY /S ' + @ServerName +
		' /U UserName+
		' /P Password+
		' /D ' + @DB +
		' /T ' + @TableName +
		' /C ' + @ColName +
		' /W "' + @WhereClause + '"' +
		' /F "' + @FileName + '"' +
		' /' + @Direction

/*Run the command prompt action*/
EXEC master..xp_cmdshell @Exec
GO

You have to execute a command line app to do the load. Well, at least that's the only way I could get it to do it!

Hope it helps!
Rob