tSQLt.XmlResultFormatter truncates results after 2033 chars

Question

Trying to export my tSQLt test results to XML with tSQLt.XmlResultFormatter. But it seems to truncate the output after 2033 characters,

BEGIN TRY EXEC tSQLt.RunAll END TRY BEGIN CATCH END CATCH; EXEC tSQLt.XmlResultFormatter

I want my output in xml so i can reference it in a Microsoft Devops CI deployment pipeline. I only have 14 tests at the moment which doesn't feel like a lot. If this is the limit of XmlResultFormatter, is there another way to get the results in an xml format?

Thanks for your time

Answer 1

You don't say what method you're using to execute the SQL commands in your question. There's probably a more streamlined way of doing this, but I solved this problem in Jenkins and then ported the solution to an Azure DevOps Command Line task with the following code, on a Windows build agent. EXEC tSQLt.RunAll ran in a previous step:

::export the test results to a file
bcp "EXEC [tSQLt].[XmlResultFormatter];" queryout %WORKSPACE%\test_results.xml -S %DBSERVER% -d %DBNAME% -T -w

::remove the carriage returns (added by BCP every 2048 chars) from the xml file
::and write to a new file
PowerShell -ExecutionPolicy Bypass -NoProfile -Command "& {(gc %WORKSPACE%\test_results.xml -Raw).replace([Environment]::NewLine , '') | Set-Content %WORKSPACE%\output_test_results.xml}"

Hopefully the comments explain what is going on.

The Command Line task has the following environment variables defined:

• DBSERVER - the database server name
• DBNAME - the name of the database under test
• WORKSPACE - $(build.sourcesDirectory) - this is a legacy of running the script in Jenkins and could be factored out

The file output by the second command output_test_results.xml is passed to a Publish Test Results task later in the build.

EDIT

I looked into this and I think I understand what's happening. Although SSMS presents an XML result as a single column/row, the data is actually returned to the client as a sequence of shorter rows (<2048 characters).

The default behaviour of Invoke-Sqlcmd is to return results as an array of DataRow objects - each item in the array contains between 2000 and 2048 characters. This array needs to be concatenated back together to generate the result set - here's one way of doing it in PowerShell:

$out = ""; Invoke-SqlCmd  -ServerInstance <server> -Database <db name> -Query "exec tSQLt.XmlResultFormatter" -MaxCharLength 1000000 | %{ $out = $out + $_[0]}; $out > c:\temp\output.txt

My original answer is also affected by this issue - hence the PowerShell command to remove carriage returns every 2048 characters.