問(wèn)題描述

我有一個(gè) SQL 查詢，我使用 For XML Path 將結(jié)果生成為 XML.

I have an SQL query and I am using For XML Path to generate the result as an XML.

誰(shuí)能幫助我將 XML 輸出轉(zhuǎn)換為a.xml"文件并保存在計(jì)算機(jī)的特定文件夾中?

Can anyone help me about converting that XML output into "a.xml" file and save in a particular folder of a computer?

也想知道，除了BCP還有什么方法可以實(shí)現(xiàn)嗎?

Also want to know, is there any method other than BCP to achieve this?

推薦答案

您可以嘗試使用 xp_cmdshell....

You could try using xp_cmdshell....

-- Read your query results into an XML variable
DECLARE @xml AS XML = (SELECT * FROM YourTable FOR XML PATH)

-- Cast the XML variable into a VARCHAR
DECLARE @xmlChar AS VARCHAR(max) = CAST(@xml AS VARCHAR(max))

-- Escape the < and > characters
SET @xmlChar = REPLACE(REPLACE(@xmlChar, '>', '^>'), '<', '^<')

-- Create command text to echo to file
DECLARE @command VARCHAR(8000) = 'echo ' + @xmlChar + ' > c:\test.txt'

-- Execute the command
EXEC xp_cmdshell @command

如果您想要更多控制，您也可以嘗試使用 Powershell 命令，例如設(shè)置編碼...

You could also try a Powershell command if you wanted a bit more control e.g. to set encoding...

DECLARE @command VARCHAR(8000) = 'powershell -Command "Set-Content -Encoding UTF8 C:\test.txt \"' + @xmlChar + '\""'

一些注意事項(xiàng)...

該命令有 8000 個(gè)字符的長(zhǎng)度限制，因此不適用于大文件.

There is an 8000 character length limit on the command, so it's no good for large files.

如果您將文件保存到映射驅(qū)動(dòng)器，它將在數(shù)據(jù)庫(kù)服務(wù)器上查找該驅(qū)動(dòng)器.因此，C:\ 將指服務(wù)器的 C:\ 驅(qū)動(dòng)器，而不是您運(yùn)行 Management Studio 的位置.

If you save the file to a mapped drive, it will look for that drive on the database server. So, C:\ will be referring to the C:\ drive of the server, not where you are running Management Studio.

運(yùn)行xp_cmdshell需要特殊權(quán)限.

點(diǎn)擊此處了解更多詳情.

Click here for more details.

這篇關(guān)于如何將 XML 查詢結(jié)果保存到文件的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！