0

I'm keen to export a SQL Server query result to an XML file.

I seem to get carriage returns in the resulting file.

I'm wondering what approach I should take to remove the carriage returns from the XML results file?

What I have tried is:

DOS command:

sqlcmd -S HSL-PC0242 -U sa -P PasswordX -i "D:\SQL\auditlog_query1.sql" -C -o "D:\SQL\auditlog_query1_out.xml"

D:\SQL\auditlog_query1.sql contains:

    SELECT
    A.*
FROM
    H2PenguinDev.[dbo].[AuditLog] A
    JOIN H2PenguinDev.dbo.ImportProviderProcesses IPP ON IPP.ImportType = 'Z' 
      AND A.OperatorID = IPP.OperatorID 
      AND A.AuditTypeID in ( '400','424','425' )
WHERE
    A.[PostTime] >= IPP.StartTime
    AND A.[PostTime] <= dateadd(second, 90, IPP.StartTime) 
    FOR XML PATH('Record'), ROOT('AuditLog')
Dale K
  • 25,246
  • 15
  • 42
  • 71
Allan F
  • 2,110
  • 1
  • 24
  • 29
  • 1
    Try `SELECT CONVERT(nvarchar(max), (...your query here...), 0)` – Charlieface Jan 19 '21 at 01:06
  • Thanks @Charlieface .. This seemed to work with use of xp_cmdshell and bcp .. I seem to get truncation with sqlcmd approach. – Allan F Jan 19 '21 at 03:12
  • Looks like nvarchar(max) can be up to 2Gb so should be fine for what I want to do .. – Allan F Jan 19 '21 at 03:16
  • EXEC xp_cmdshell 'bcp "SELECT CONVERT(nvarchar(max), (SELECT A.* FROM H2PenguinDev.dbo.AuditLog A JOIN H2PenguinDev.dbo.ImportProviderProcesses IPP ON IPP.ImportType = ''Z'' AND A.OperatorID = IPP.OperatorID AND A.AuditTypeID in (''400'',''424'',''425'' ) WHERE A.[PostTime] >= IPP.StartTime AND A.[PostTime] <= dateadd(second, 90, IPP.StartTime) FOR XML PATH(''Record''), ROOT(''AuditLog'')), 0)" queryout "D:\bcptest.xml" -T -c -t,' – Allan F Jan 19 '21 at 03:17

1 Answers1

1

This seems to work.

2Gb output limit tho .. which is fine for this case.

Can open resulting XML in excel ..

and/or use notepad XML plugin and pretty print option to view ..

Note the requirement for ## temp tables rather than single # temp table name.

SELECT A.MyXML
INTO ##AuditLogTempTable
FROM
(SELECT CONVERT(nvarchar(max), 
    (
            SELECT
                A.*
            FROM
                [dbo].[AuditLog] A
                JOIN ImportProviderProcesses IPP ON IPP.ImportType = 'Z' 
                  AND A.OperatorID = IPP.OperatorID 
                  AND A.AuditTypeID in ( '400','424','425' )
            WHERE
                A.[PostTime] >= IPP.StartTime
                AND A.[PostTime] <= dateadd(second, 90, IPP.StartTime) 
                FOR XML PATH('Record'), ROOT('AuditLog')
        )
    , 0
    )   AS MyXML
) A

EXEC xp_cmdshell 'bcp "SELECT MyXML FROM ##AuditLogTempTable" queryout "D:\bcptest1.xml" -T -c -t,'
Allan F
  • 2,110
  • 1
  • 24
  • 29
  • 1
    Especially for export, consider [explicitly specifying columns](https://stackoverflow.com/q/3639861/1422451) instead of `*`. – Parfait Jan 19 '21 at 04:52