SQL XML output into file automatically-CodePudding

I have the code below and works OK, generates the XML output in SQL Server Management Studio. How can i save the result into a file on the drive automatically in order to run every night?

Thank you in advance for your help.

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>');

CodePudding user response：

Please try the following solution based on bcp.

First, run it as-is to see how it is working.

Later on package your T-SQL in the question as a stored procedure.

And modify the following variable as follows:

@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;

CodePudding user response：

Or in Powershell like this

$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()