从 SQL Server 2008 生成 XML 文件

Question

提问by Prasanna

I am working on an application where I need to get the SQL response as XML into an XML file (and to store it in some physical location, say c:\xyz.xml).

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

I am able to generate the XML content using the provisions available in SQL Server as shown below.

我能够使用 SQL Server 中可用的规定生成 XML 内容，如下所示。

 SELECT * FROM @Table FOR XML AUTO, ELEMENTS

where: @Tableis a table variable.

其中：@Table是表变量。

I want to know how I can store the query output to an XML file from SQL Server itself.

我想知道如何将查询输出从 SQL Server 本身存储到 XML 文件。

Answer 1

回答by Nikita G.

There's one more option - use sqlcmd tool.

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

Add :XML ONas a first line in your SQL file (let's call it input.sql)
A command like this will do the trick:

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

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

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

Answer 2

回答by Gausie

You need to use xp_cmdshell, and the bcp utilityin the following way

您需要按以下方式使用xp_cmdshell和bcp 实用程序

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

Hit me back in the comments if you've got any questions or want to know anything more about how this works.

如果您有任何问题或想了解有关其工作原理的更多信息，请在评论中回复我。

Answer 3

回答by Remus Rusanu

You can't write to the file system from SQL Server itself. At least not that easily. There are three alternatives:

您不能从 SQL Server 本身写入文件系统。至少没那么容易。有以下三种选择：

use xp_cmdshell. I would strongly advise against it. By default xp_cmdshellis disabledfor security purposes, and to have it enabled it just for this operation opens a way to big security hole in your system.
use the FileSystemObjectand the OLE Automation procedures sp_OACreate/sp_OAMethod. See Reading and Writing Files in SQL Server using T-SQL. This, while marginally better than the xp_cmdshell option, it doesn't give a much better security story. The only reason why is better than xp_cmdshell is that is by far less known by hackers. But the OLE Automation procedures optionin SQL Server is also disabled by default and enabling this option exposes the very same security problems xp_cmdshell has.
use a CLR procedure. This would be my recommendation. Create an assembly with digital signature, use the assembly signature to allow, through Transact-SQL code signing, EXTERNAL ACCESS, then use the CLR procedure to write the XML into the file system. While this is significantly more complex than the simple xp_cmdshell or OLE Automation options, it is the most controlable and granular one from a security point of view and is the easiest to maintain and get right (is .Net code as opposed to shell scripts). Unfortunately, by default the clr optionis also disabled in the server and has to be enabled.

使用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 选项在服务器中也被禁用，必须启用。

Answer 4

回答by Mark Dickinson

If you press

如果你按

ctrl + shift + f

you will have selected "Results To File." This can be found in the Query menu on the top bar of Sql Management Studio.

您将选择“结果归档”。这可以在 Sql Management Studio 顶部栏的查询菜单中找到。

Or put something like this into your sql script

或者把这样的东西放到你的 sql 脚本中

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

See this link, there is an issue about whether it is the app's c drive or the sql server's c drive. Have fun sorting that out.

看这个链接，是app的c盘还是sql server的c盘有问题。好好整理一下吧。

Answer 5

回答by Svetlozar Angelov

You can create CLR function that create the file, build it into the sql server, and use it from a stored procedure

您可以创建创建文件的 CLR 函数，将其构建到 sql server 中，并从存储过程中使用它

Another way( I haven't tested it ) - There is a tool bcp

另一种方式（我没有测试过） - 有一个工具 bcp

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

This example is from here

这个例子来自这里

Answer 6

回答by user2146042

Simple SQL Write to File method

简单的 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'

Answer 7

回答by Alex

If your xml output is relatively small (<4000 characters), then you can use this SP:

如果你的 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

If you don't work with Unicode, then you can create another SP: USP_WRITE_NON_UNICODE_STRING_TO_FILE, which will be very similar to the previous one with the following changes:

如果您不使用 Unicode，那么您可以创建另一个 SP：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));

That SP allows the use of two times longer the input string (<8000 characters).

该 SP 允许使用两倍长的输入字符串（<8000 个字符）。

If your XML is longer than 8000 but less than 1MB you can use sqlcmd utilitywithout :XML ON command. It greatly simplify the usage of the utility because you don't need a separate input_file with :XML ON command included. Here is an example:

如果您的 XML 长于 8000 但小于 1MB，您可以使用sqlcmd 实用程序而无需 :XML ON 命令。它极大地简化了该实用程序的使用，因为您不需要包含 :XML ON 命令的单独 input_file。下面是一个例子：

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;

You can also use an SP here:

您也可以在此处使用 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

If your XML is more than 1MB you should use :XML ON command in a separate script and specify it as -i input_file parameter.

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

Answer 8

回答by Bojan Nedeljkovic

I made this SP so I can easily extract data from db or temp table to XML file on file system. It supports where clause.

我制作了这个 SP，所以我可以轻松地从 db 或临时表中提取数据到文件系统上的 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

从 SQL Server 2008 生成 XML 文件

提问by Prasanna

回答by Nikita G.

回答by Gausie

回答by Remus Rusanu

回答by Mark Dickinson

回答by Svetlozar Angelov

回答by user2146042

回答by Alex

回答by Bojan Nedeljkovic

相关推荐

最近更新

标签

从 SQL Server 2008 生成 XML 文件

提问by Prasanna

回答by Nikita G.

回答by Gausie

回答by Remus Rusanu

回答by Mark Dickinson

回答by Svetlozar Angelov

回答by user2146042

回答by Alex

回答by Bojan Nedeljkovic

相关推荐

SQL 如何在 if else 语句中设置多个值？

SQL 查询中的 sql-server、IF 语句

将十进制数转换为 INT SQL

SQL 选择不同的值，但按不同的值排序

相关推荐

最近更新

标签