sql-server-2008 - 不用 SSIS 或 xp_cmdshell 将 SQL 查询结果保存到 XML 文件

Question

几天来我一直在寻找我遇到的问题的解决方案。

我有一个 SQL 服务器查询，它以 XML 格式生成我需要的一些输出，所以这是查询:

SELECT FinishCode, reason, COUNT(reason) as Numbers
  FROM [I3_Dialer].[dbo].[I3_UKPIPELINEWORKFLOW_CH0]
 WHERE callplacedtime > (select cast(convert(varchar(10), getdate(), 110) as datetime)) and reason = 'Success'
 GROUP BY reason, finishcode
 ORDER BY Numbers
 FOR XML PATH('FinishCode'), ROOT('UK_Products_Pipeline'); 

该输出生成一个 XML，但我需要将其存储为一个文件并作为作业每 xx 分钟运行一次。

到目前为止，我找到的所有解决方案都将 BCP 与 xp_cmdshell 或 SSIS 结合使用。我发现在 SQL 服务器上唯一可用的是 BCP(令我惊讶的是)并且服务器没有足够的资源来安装 SSIS，SQL 管理员也不想启用或安装其他软件。

我试图用它来运行一个作业，但它生成的文件包含很多额外的信息，这些信息弄乱了 XML。

欢迎提出任何建议，因为我是 SQL 新手。

Answer 1

经过长时间的挖掘，我找到了解决问题的办法:

我创建了一个这样的存储过程:

USE [DATABASE NAME]
GO
/****** Object:  StoredProcedure [dbo].[asp_Write_String_To_File]    Script Date:   11/21/2013 09:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [PROCEDUER NAME] 
  (
    @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

然后在查询中我这样调用存储过程:

USE [DATABASE NAME]
declare @xml nvarchar(max)
declare @FilePath nvarchar(255)
declare @FileName nvarchar(255)
set @FilePath = '[Path to store file]'       -- Location to store the file
set @FileName = '[File Name to store].xml'   -- This is the XML filename

BEGIN
  set @xml =
      (
        SELECT ( -- Add This
               SELECT FinishCode, Reason, COUNT(*) AS Numbers
                 FROM [DATABASE] ch WITH (NOLOCK)
                WHERE [Condition 1]
                  AND [Condition 2]
        GROUP BY Reason, FinishCode
                ORDER BY Numbers
              FOR XML PATH('FinishCode'),
                 ROOT('UK_Products_Pipeline'),
                 TYPE
                ) -- Add This
         FOR XML PATH('Dial_Stats') -- Add This
   )

exec asp_Write_String_To_File @xml, @FilePath, @FileName

END

然后调用存储过程并将文件写入您的