我有下面的代碼并且作業正常，在 SQL Server Management Studio 中生成 XML 輸出。如何將結果自動保存到驅動器上的檔案中以便每晚運行？
預先感謝您的幫助。
DECLARE @Rechnungen TABLE (id int , Nummer nvarchar(20), Datum datetime);
INSERT INTO @Rechnungen (id, Nummer, Datum) VALUES
(8, 'R200001', '2020-06-29');
DECLARE @Rechnungpos TABLE (id int, id_Rechnung int, Anzahl float);
INSERT INTO @RechnungPos (id, id_Rechnung, Anzahl) VALUES
(1, 8, 3),
(5, 8, 1),
(9, 8, 2);
-- DDL and sample data population, end
DECLARE @ID_Rechnung int = 8;
WITH XMLNAMESPACES ('urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' as ext
, 'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as cbc
, 'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as cac
, 'http://uri.etsi.org/01903/v1.3.2#' as xades
, 'http://www.w3.org/2001/XMLSchema-instance' as xsi
, 'http://www.w3.org/2000/09/xmldsig#' as ds)
SELECT (
SELECT '2.1' AS [cbc:UBLVersionID],
'TR1.2' AS [cbc:CustomizationID],
'' AS [cbc:ProfileID],
p.Nummer AS [cbc:ID],
'false' AS [cbc:CopyIndicator],
'' AS [cbc:UUID],
CAST(p.Datum AS Date) AS [cbc:IssueDate],
(
SELECT c.id AS [cbc:ID]
, CAST(c.Anzahl AS INT) AS [cbc:InvoicedQuantity]
FROM @Rechnungpos AS c INNER JOIN
@Rechnungen AS p ON p.id = c.id_Rechnung
FOR XML PATH('r'), TYPE, ROOT('root')
)
FROM @Rechnungen AS p
WHERE p.id = @ID_Rechnung
FOR XML PATH(''), TYPE, ROOT('Invoice')
).query('<Invoice xmlns:ds="http://www.w3.org/2000/09/xmldsig#"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xades="http://uri.etsi.org/01903/v1.3.2#"
xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"
xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"
xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2">
<ext:UBLExtensions>
<ext:UBLExtension>
<ext:ExtensionContent/>
</ext:UBLExtension>
</ext:UBLExtensions>
{
for $x in /Invoice/*[local-name()!="root"]
return $x,
for $x in /Invoice/root/r
return <cac:InvoiceLine>{$x/*}</cac:InvoiceLine>
}
</Invoice>');
uj5u.com熱心網友回復：
請嘗試以下基于bcp的解決方案。
首先，按原樣運行它，看看它是如何作業的。
稍后將問題中的 T-SQL 打包為存盤程序。
并修改如下變數：
@SQL VARCHAR(2048) = 'EXEC <your_stored_procedure_name>;'
SQL
/*
-T tells BCP to use Windows Authentication to connect to SQL Server. It will operate as the user who's running the command.
-U <login_id> -P <password> tells BCP to use SQL Server account while connecting to SQL Server.
*/
DECLARE @SQLCmd VARCHAR(8000)
, @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml'
, @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe'
, @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');'
, @tcpPort VARCHAR(10) = '1433'
, @WindowsAuth BIT = 1 -- 1 is for Windows Authentication, 0 is for SQL Server Authentication
, @loginID VARCHAR(100) = 'loginID'
, @password VARCHAR(100) = 'password'
, @serverName VARCHAR(100) = 'realServerName';
-- SQL Server 2016 onwards
--SET @SQLCmd = FORMATMESSAGE('START "" "%s" "%s" queryout "%s" -T -x -c -C 1252 -a 65535 -S "%s,%s"'
-- , @bcp
-- , @SQL
-- , @outputFileName
-- , @@SERVERNAME
-- , @tcpPort);
-- older versions of SQL Server
-- /B "WindowTitle" parameters produce output in the SSSMS !!!
SET @SQLCmd = 'START /B "WindowTitle" "' @bcp '"'
' "' @SQL '"'
' queryout "' @outputFileName '"'
-- IIF(@WindowsAuth = 1, ' -T', ' -U ' @loginID ' -P ' @password)
CASE WHEN @WindowsAuth = 1 THEN ' -T'
ELSE ' -U ' @loginID ' -P ' @password
END
' -x -c -C 1252 -a 32768'
' -S "' @serverName ',' @tcpPort '"';
-- just to see it
SELECT @SQLCmd AS [Command to execute];
-- create file on the file system
EXECUTE master.sys.xp_cmdshell @SQLCmd;
uj5u.com熱心網友回復：
或者像這樣在Powershell中
$t = invoke-sqlcmd "select * from sys.messages for xml path('row'), root('data')"
$sw = new-object system.io.streamwriter "c:\temp\foo.xml"
foreach($r in $t) { $sw.write($r[0]) }
$sw.close()
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/492130.html