CODEWITHSUNDEEP
sqlsql-server
Allan F
Allan F

Reputation: 2298

Export query result to an XML file - removing carriage return from XML result file?

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

Upvotes: 0

Views: 174

Answers (1)

Allan F
Allan F

Reputation: 2298

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,'

Upvotes: 1

Related Questions