Property "XML*" cannot be found

[NowinskiK]

When I run the task to run unit test against tSQLtExample

##[error]Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException: Test Case Summary: 11 test case(s) executed, 10 succeeded, 0 skipped, 1 failed, 0 errored.
##[debug]Processed: ##vso[task.logissue type=error;]Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException: Test Case Summary: 11 test case(s) executed, 10 succeeded, 0 skipped, 1 failed, 0 errored.
------------------------------------------------------------------------------------------ 
 Msg 50000, Level 16, State 10, Procedure , Line 1. ---> System.Data.SqlClient.SqlException: Test Case Summary: 11 test case(s) executed, 10 succeeded, 0 skipped, 1 failed, 0 errored.
------------------------------------------------------------------------------------------
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteBatch(String batch)
   --- End of inner exception stack trace ---
Tests ran successfully. Saving test results to D:\a\1\s\tSQLt-out\TEST-Results.xml
##[error]System.Management.Automation.PSArgumentException: Property "XML*" cannot be found.
##[debug]Processed: ##vso[task.logissue type=error;]System.Management.Automation.PSArgumentException: Property "XML*" cannot be found.
Finished gathering test results and writing it to D:\a\1\s\tSQLt-out\TEST-Results.xml
##[error]PowerShell script completed with 2 errors.
Finishing: Run tSQlt Unit Tests

Task : Run tSQLt Unit Tests
Description : Run tSQLt Unit Tests and extract its results and Code Coverage
Version : 0.2.3

---

Then, in the subsequent task I've got the following error:
##[warning]Failed to read D:\a\1\s\tSQLt-out\TEST-Results.xml. Error : Root element is missing..

Task : Publish Test Results
Description : Publish test results to Azure Pipelines
Version : 2.171.0
Author : Microsoft Corporation

Link to my build (for my record purposes)

[Gwayaboy]

I was going to raise a similar @NowinskiK issue and I assume you're also using Microsoft hosted agent within Azure DevOps Pipeline (not the on-premises version).
After looking through the source code I've found that the issue is around the last Invoke-SqlCmd command (line 26-27) at
https://github.com/riserrad/RuntSQLtTests-AzDO/blob/master/buildAndReleaseTask/Invoke-tSQLtTests.ps1
I've managed to run the same command locally and it turns out the piped Select-Object -ExpandProperty XML* is crashing

I've added an extra PowerShell inline step (could be an external parameterised ps1 script) in my YAML & Classic pipelines as a workaround to export the TEST-Results.xml

(Invoke-SqlCmd -ConnectionString $(connectionString) -QueryTimeout $(queryTimeOut) -Query "EXEC [tSQLt].[XMLResultFormatter]")[0] | Out-File -FilePath $(System.DefaultWorkingDirectory)\tSQLt-out\TEST-Results.xml -NoNewLine

You're following publish test result should then pick up the Junit TEST-Results.xml and surface the test run with the pipeline execution.

@riserrad and @Waylon58 I am happy to fork and make a Pull Request to implement the fix.
When running the task using windows Microsoft hosted agents there's a warning about using a deprecated task execution handler. see below:
##[warning]Task 'runtsqltunittests' (0.2.3) is using deprecated task execution handler. The task should use the supported task-lib: https://aka.ms/tasklib

There's also an error with SQLCover in a similar setup. I might raise a separate issue for this (if none opened)

[JovialJerboa]

A quick addition to @Gwayaboy's comment.

The default MaxCharLength value for Invoke-SqlCmd is 4000, so if your xml is longer than that it will be truncated. If you're using this workaround I'd recommend passing in a larger value e.g.

# Bug in runtsqltunittests
# https://github.com/riserrad/RuntSQLtTests-AzDO/issues/23?_pjax=%23js-repo-pjax-container
- pwsh: |
    $SqlMaxCharLength = [int]::MaxValue
    (Invoke-SqlCmd -ConnectionString '$(SqlConnectionString)' -QueryTimeout '$(SqlQueryTimeout)' -Query "EXEC [tSQLt].[XMLResultFormatter]" -MaxCharLength $SqlMaxCharLength)[0] | Out-File -FilePath $(System.DefaultWorkingDirectory)\tSQLt-out\TEST-Results.xml -NoNewLine
  displayName: 'Export test results (bug workaround)'