Hi,
I need to create an XML file from a string in a stored procedure. I cannot use ForXML as I cannot create XML as per our requirement. I tried sp_OACreate but didn't help much.
Following is the sp used to create XML string which should be stored as an XML file/ I tried using using sp_OACreate & other sps. Can any one help me to create an XML file from a string. I did try creating XML from a string using bcp, it worked fine but it require userid and psw to be hardcoded. Please help..itz very urgent
Code:
Create PROCEDURE [sp_TestXML2]
	@XMLOUT		varchar(8000)		OUTPUT
AS
SET NOCOUNT ON

DECLARE
	@XML 		varchar(8000),	
	@strRow			varchar(50),
	@RowId			int,
	@Rowcount		int,
	@cmd 		varchar(8000)
	

Begin --Main

	SELECT @Rowcount = count(*) FROM SNP_ORS_Table_Create_XML(NOLOCK)
	SET @XML = '<?xml version="1.0" encoding="UTF-8" ?>'
	SET @XML = @XML + '<tma>'
	SET @XML = @XML + '<tma_header>'
	SET @XML = @XML + '<conversationid></conversationid>'
	SET @XML = @XML + '<transaction>TRAN29D</transaction>'
	SET @XML = @XML + '<sourcecountry>XXXX</sourcecountry>'
	SET @XML = @XML + '<sourcecompany>YYYYY</sourcecompany>'
	SET @XML = @XML + '<sourcesystem>ZZZZ</sourcesystem>'
	SET @XML = @XML + '</tma_header>'
	SET @XML = @XML + '<tma_body>'
	SET @XML = @XML + '<bankfiletype>NB</bankfiletype>'
	SET @XML = @XML + '<generateddate>28-Nov-07</generateddate>'
	SET @XML = @XML + '<records>'

	While (@Rowcount > 0)
		BEGIN
		Set Rowcount  1	
Select @RowId = recId,@strRow = record From SNP_ORS_Table_Create_XML(NOLOCK)
SET @XML = @XML + '<record>' + LTRIM(RTRIM(@strRow)) + '</record>'
Delete From SNP_ORS_Table_Create_XML Where recId = @RowId
			Set @Rowcount = @Rowcount-1
		END

	SET @XML = @XML + '</records>'
	SET @XML = @XML + '</tma_body>'
	SET @XML = @XML + '<tma_fault>'
	SET @XML = @XML + '<systemfault/>'
	SET @XML = @XML + '<applicationfault/>'
	SET @XML = @XML + '</tma_fault>'
	SET @XML = @XML + '</tma>'

--	set @cmd = 'bcp "Select ''' + @XML + '''" queryout c:\myfile11.xml -S01HW99474 -Usa -Peseg -c -r\n -t'
--	exec master..xp_cmdshell @cmd

	exec sp_AppendToFile_XML 'c:\xmlvvvtest.xml',@XML
End --Main
Thnak you
shan