How to generate a xml doc using from database using T-sql

Hello Pankaj,

Please try the below code,

------------------- data definition
create table [XML]([xml] nvarchar(max),xmlname nvarchar(50))
insert into [XML] values ('<TEST><NAME>Testing</NAME></TEST>','XMLFILE1')
insert into [XML] values ('<TEST><NAME>Testing1</NAME></TEST>','XMLFILE2')
--------------------
select * from [XML]
-------------------  FINAL QUERY
DECLARE @path VARCHAR(50)   
DECLARE @sqlStr VARCHAR(1000) 
DECLARE @sqlCmd VARCHAR(1000)   
SET @path = 'C:\TESTING\'
SELECT @sqlStr=[xml] from [XML]
EXECUTE spWriteStringToFile @sqlStr , @path, 'XMLFILE1.xml'
-----------
------------------ Stored Procedure
CREATE PROCEDURE spWriteStringToFile
 (
@String Varchar(max), --8000 in SQL Server 2000
@Path VARCHAR(255),
@Filename VARCHAR(100)
--
)
AS
DECLARE  @objFileSystem int
        ,@objTextStream int,
                @objErrorObject int,
                @strErrorMessage Varchar(1000),
            @Command varchar(1000),
            @hr int,
                @fileAndPath varchar(80)
set nocount on
select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT
Select @FileAndPath=@path+'\'+@filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod   @objFileSystem   , 'CreateTextFile'
        , @objTextStream OUT, @FileAndPath,2,True
if @HR=0 Select @objErrorObject=@objTextStream, 
        @strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Write', Null, @String
if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'
if @hr<>0
        begin
        Declare 
                @Source varchar(255),
                @Description Varchar(255),
                @Helpfile Varchar(255),
                @HelpID int
        
        EXECUTE sp_OAGetErrorInfo  @objErrorObject, 
                @source output,@Description output,@Helpfile output,@HelpID output
        Select @strErrorMessage='Error whilst '
                        +coalesce(@strErrorMessage,'doing something')
                        +', '+coalesce(@Description,'')
        raiserror (@strErrorMessage,16,1)
        end
EXECUTE  sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream

---

Regards, RSingh