从SQL Server 2008生成XML文件

Question

从SQL Server 2008生成XML文件

sqlsql-serverxmlsql-server-2008

8

我正在开发一个应用程序，需要将SQL响应作为XML获取到XML文件中（并将其存储在某个物理位置，比如c:\xyz.xml）。

我可以使用SQL Server提供的功能生成XML内容，如下所示。

 SELECT * FROM @Table FOR XML AUTO, ELEMENTS

where: @Table 是一个表变量。

我想知道如何从 SQL Server 将查询结果存储为 XML 文件。

- Prasanna

8个回答

5

您需要使用xp_cmdshell和bcp实用程序来执行以下操作：

EXEC xp_cmdshell 'bcp "SELECT * FROM @Table FOR XML AUTO, ELEMENTS" queryout "C:\table.xml" -c -T'

如果你有任何问题或想了解更多关于这个如何工作的信息，请在评论中回复我。

- Gausie

你需要为表格指定目录和模式（即@Table应该是[数据库].dbo.[表格]的格式）。 - Gausie

5

你无法直接从SQL Server向文件系统写入数据，至少不是那么容易。有三种替代方案：

使用xp_cmdshell。我强烈建议不要这样做。默认情况下，xp_cmdshell已被禁用以提高安全性，并且仅为此启用它会在系统中打开一个大的安全漏洞。
使用FileSystemObject和OLE自动化程序sp_OACreate/sp_OAMethod。请参阅使用T-SQL在SQL Server中读写文件。虽然这比xp_cmdshell选项稍微好一些，但它并没有提供更好的安全性保障。唯一比xp_cmdshell更好的原因是它被黑客知晓得少得多。但是，在SQL Server中，OLE自动化程序选项也是默认禁用的，启用此选项会暴露与xp_cmdshell相同的安全问题。
使用CLR过程。这是我的建议。创建一个带有数字签名的程序集，使用程序集签名通过Transact-SQL代码签名允许EXTERNAL ACCESS，然后使用CLR过程将XML写入文件系统。虽然这比简单的xp_cmdshell或OLE自动化选项复杂得多，但从安全角度来看，它是最可控和细粒度的，并且最容易维护和正确使用（是.Net代码而不是shell脚本）。不幸的是，默认情况下，clr选项也在服务器上禁用，必须启用。

- Remus Rusanu

0

如果您的XML输出相对较小（＜4000个字符），那么您可以使用此SP：

IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.USP_WRITE_UNICODE_STRING_TO_FILE') AND type = 'P')
BEGIN
    DROP PROCEDURE dbo.USP_WRITE_UNICODE_STRING_TO_FILE
END
GO

-- =============================================
-- Description: Writes the specified Unicode string to the specified file.
-- Permissions: This stored procedure uses xp_cmdshell which is disabled by default. To enable it:
--              1. In Management Studio connect to a component of SQL Server.
--              2. In Object Explorer, right-click the server, and then click Facets.
--              3. In the View Facets dialog box, expand the Facet list, and select the Surface Area Configuration.
--              4. In the Facet properties area, select XPCmdShellEnabled property and set its value to True.
--              5. Click OK.
-- Example:     EXEC dbo.USP_WRITE_UNICODE_STRING_TO_FILE'<root> <a b="c" /> </root>', 'C:\Test.xml', 1;
-- =============================================
CREATE PROCEDURE dbo.USP_WRITE_UNICODE_STRING_TO_FILE
(
    @Str            NVARCHAR(4000),
    @XmlFilePath    NVARCHAR(256),
    @Debug          BIT = 0
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Str1 NVARCHAR(MAX),
            @Cmd NVARCHAR(4000),
            @MaxLen int = 4000;

    --see: http://technet.microsoft.com/en-us/library/bb490897.aspx
    SET @Str1 = REPLACE(REPLACE(REPLACE(@Str, '>', '^>'), '<', '^<'), '"', '^"');
    -- '>' Writes the command output to a file 
    SET @Str1 =N'ECHO ' + @Str1  + N'>"'+ @XmlFilePath + N'"';
    IF @Debug = 1
    BEGIN
        DECLARE @Msg varchar(128) = 'The total lenght is ' + CAST(LEN(@Str1) AS VARCHAR(10)) + ' characters.'
        PRINT @Msg;
        PRINT @Str1;
    END

    IF (LEN(@Str1) > @MaxLen)
        RAISERROR  ('The input string is too long', 11, 0);
    ELSE
        SET @Cmd = CAST (@Str1 AS NVARCHAR(4000));

    EXEC master..xp_cmdshell @Cmd, NO_OUTPUT;           
END
GO

--Test 1
DECLARE @Str NVARCHAR(4000);
DECLARE @Xml xml = '<root> <a b="c" /> </root>';
SET @Str = CAST (@Xml AS NVARCHAR(4000));
EXEC dbo.USP_WRITE_UNICODE_STRING_TO_FILE @Str, 'C:\Test.xml', 1;
GO
--Test 2
DECLARE @Str NVARCHAR(4000);
SET @Str = REPLICATE('a', 4000);
EXEC dbo.USP_WRITE_UNICODE_STRING_TO_FILE @Str, 'C:\Test.xml', 1;
GO

如果您不使用Unicode，那么您可以创建另一个存储过程：USP_WRITE_NON_UNICODE_STRING_TO_FILE，它与之前的存储过程非常相似，但有以下更改：

    CREATE PROCEDURE dbo.USP_WRITE_NON_UNICODE_STRING_TO_FILE
    (
        @Str            VARCHAR(8000),
        @XmlFilePath    NVARCHAR(256),
        @Debug          BIT = 0
    )
    AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @Str1 VARCHAR(MAX),
                @Cmd VARCHAR(8000),
                @MaxLen int = 8000;
        ...
        SET @Cmd = CAST (@Str1 AS VARCHAR(8000));

该存储过程允许使用两倍于输入字符串的长度（<8000个字符）。

如果您的XML文件长度超过8000但小于1MB，则可以使用sqlcmd实用程序而无需使用：XML ON命令。这大大简化了实用程序的使用，因为您不需要单独的input_file，并包含：XML ON命令。以下是一个示例：

DECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'sqlcmd -S ' + @@SERVERNAME + N' -d ' + DB_NAME() + 
N' -Q "SET NOCOUNT ON; DECLARE @Xml xml = ''<root> <a >b</a> </root>''; SELECT CONVERT(NVARCHAR(MAX), @Xml);" -o "C:\Test.xml" -y 0';
PRINT @Cmd;
EXEC master..xp_cmdshell @Cmd, NO_OUTPUT;

你也可以在这里使用 SP：

CREATE PROCEDURE dbo.USP_SAMPLE_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Xml xml;
    SET @Xml = (SELECT name, type_desc FROM sys.objects FOR XML PATH('object'), ROOT('sys.objects'));
    SELECT CONVERT(NVARCHAR(MAX), @Xml)
END
GO

DECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'sqlcmd -S ' + @@SERVERNAME + N' -d ' + DB_NAME() + 
N' -Q "EXEC dbo.USP_SAMPLE_PROCEDURE;" -o "C:\Test.xml" -y 0';
PRINT @Cmd;
EXEC master..xp_cmdshell @Cmd, NO_OUTPUT;
GO

如果您的XML文件超过1MB，您应该在单独的脚本中使用:XML ON命令，并将其指定为-i输入文件参数。

- Alex

0

如果你按下

ctrl + shift + f

您将选择“结果保存到文件”。此选项可在Sql Management Studio顶部菜单的查询菜单中找到。

或者，您可以将类似以下内容放入您的SQL脚本中：

exec xp_cmdshell 'bcp "select * from suppliers" queryout "c:\suppliers.txt" -S server -T'

请查看此链接，关于是应用程序的C盘还是SQL服务器的C盘的问题。祝你好运解决。

- Mark Dickinson

0

您可以创建CLR函数来创建文件，将其构建到SQL服务器中，并从存储过程中使用它

另一种方法（我还没有测试）- 有一个名为bcp的工具

bcp "Select * from dbo..table FOR XML RAW" queryout c:\temp\test.xml -Soc-db -Uuser -Ppassword

这个例子来自这里

- Svetlozar Angelov

0

简单的SQL写入文件方法

DECLARE @xml XML = '<MyXML></MyXMl>'
DECLARE @strXML varchar(max) = convert(varchar(max),@XML) 

-- Add white space for readability 
SELECT @strxml = replace(@strxml,'</',char(13) + char(10) + '</')
--- Add Declartives, namespaces and xls 

Create Table dbo.BCP_OUT(contents varchar(max)) 
INSERT INTO dbo.bcp_out(contents)
SELECT Convert(varchar(max),@strXML ) 

EXEC xp_cmdshell N'BCP -S SERVER [database].dbo.bcp_out -T -c -o \\pathto\file.name'

- user2146042

0

我创建了这个存储过程，以便能够轻松地从数据库或临时表中提取数据，并将其保存为文件系统上的XML文件。它支持where子句。

CREATE PROCEDURE dbo.ExportToXMLFile
      @TableName varchar(1000)
    , @Where varchar(2000)=''
    , @TicketNumber varchar(500)
    , @debug bit=0
as
/*
    Date:2016-03-27
    Author: BojNed
    Purpose: Exports data from table to XML file on filesystem. 
    @TableName = name of table to export. 
    @Where = optitional, to set @Where Clause. DO NOT ENTER WHERE at beggining of the string 
    @TicketNumber = To save to folder on filesystem
    @Debug = Optitional. To debug this SP.

    Examples:
    EXEC dbo.ExportToXMLFile '#tt','columnX=2','221',0
    EXEC dbo.ExportToXMLFile '[Production].[Product]','','252',1
    EXEC dbo.ExportToXMLFile '[dbo].[DatabaseLog]','ColumnZ=55','351',0
    EXEC dbo.ExportToXMLFile '[dbo].[DatabaseLog]','','7865',1
*/
begin 

    if @debug=0
        SET NOCOUNT ON

    declare @SQL nvarchar(max)
    declare @IsTempTable bit
    declare @NewTableName varchar(1000)
    declare @Xml as XML

    if (isnull(@TicketNumber,''))=''
    begin
        RAISERROR('No ticket number defined',16,1,1)
        RETURN
    END

    --check if table is tmp or variable
    if (SELECT SUBSTRING(@TableName,1,1))='#' or (SELECT SUBSTRING(@TableName,1,1))='@'
    BEGIN
        if @debug=1
            PRINT 'Source is TMP table'
        set @NewTableName='TMPTBL_'+@TableName
    END
    ELSE
    BEGIN
        if @debug=1
            PRINT 'Source is db table'
        set @NewTableName=replace(@TableName,'.','_')
    END

        --RemoveSpecialChars
        declare @KeepValues varchar(1000)
        set @KeepValues = '%[^a-z^0-9^_]%'
        WHILE PATINDEX(@KeepValues,@NewTableName)>0
        set @NewTableName = STUFF(@NewTableName, PATINDEX(@KeepValues,@NewTableName),1,'')

    if @debug=1
        PRINT 'Node name for XML Header and filename: '+@NewTableName

    if ISNULL(@Where,'')=''
    BEGIN
        set @SQL= 'SELECT * FROM '+ @TableName+' FOR XML PATH, ROOT ('''+@NewTableName+'''), ELEMENTS'
        if @debug=1
            PRINT 'NO Where condition'
    END
    ELSE
    BEGIN
        set @SQL= 'SELECT * FROM '+ @TableName+' WHERE '+@Where+ ' FOR XML PATH, ROOT ('''+@NewTableName+'''), ELEMENTS'
        if @debug=1
            PRINT 'With Where condition'
    END

    --Get XML to tbl
    if ISNULL(OBJECT_ID ('tempdb..##TXML'),0)>0
    DROP TABLE ##TXML

    CREATE TABLE ##TXML (XMLText XML)

    set @SQL =  ' insert into ##TXML select ('+@SQL+')'

    --parse query
    declare @testsql nvarchar(max)
    declare @result int
    set @testsql = N'set parseonly on; ' + @sql
    exec @result = sp_executesql @testsql

    -- If it worked, execute it
    if @result = 0
        begin
            if @debug=1
                PRINT 'Query OK: '+ @SQL
            exec sp_executesql @sql
        end
    else 
    BEGIN
        DECLARE @msg varchar(2000)
        set @msg ='Parsing Error on query: ' + @SQL
        RAISERROR (@msg,16,1,1)
        RETURN
    END

    DECLARE @Tbl TABLE (id int identity(1,1), Dir varchar(256))

    --check if dir exsists
    INSERT into @Tbl
    EXEC master.dbo.xp_subdirs 'C:\DataCorrectionBackup\'


    if (SELECT Count(*) from @Tbl WHERE Dir=@TicketNumber)=0
    BEGIN
        --create new dir
        DECLARE @t varchar(500)
        set @t ='C:\DataCorrectionBackup\'+@TicketNumber

        EXEC master.sys.xp_create_subdir @t
    END

    declare @bcp varchar(500)
    declare @Filename VARCHAR(255)

    set @Filename =convert(varchar(100),GETDATE(),112)+'_'+replace(convert(varchar(100),GETDATE(),114),':','')+'_'+@NewTableName+'.xml'

    set @bcp = 'bcp "SELECT XMLText from ##TXML" queryout C:\DataCorrectionBackup\'+@TicketNumber+'\'+@Filename+' -w -T -S'+ @@servername

    --save file
    if @debug=0
        EXEC xp_cmdshell @bcp, NO_OUTPUT
    ELSE 
    BEGIN
        EXEC xp_cmdshell @bcp
        PRINT @bcp
    END

    DROP table ##TXML

end

go

- Bojan Nedeljkovic

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Nikita R. · Accepted Answer

还有一种选择 - 使用sqlcmd工具。

在你的SQL文件中添加:XML ON作为第一行（我们称之为input.sql）
像这样的命令就可以解决问题：

sqlcmd -S <your-server> -i input.sql -o output.xml