A common question that I've seen online is how do I return multiple sql datasets from a single query using INVOKE-SQLCMD. Most of the answers out there say it is not possible, but it is in fact possible by using the OutputAs dataset parameter. This parameter outputs a dataset that has all of the information needed. The code below will setup our script and save the return data into a Powershell variable called $ds that we can use later.

$ServerInstance = sname
$sql = "SELECT
    SERVERPROPERTY('MachineName') MachineName
  , SERVERPROPERTY('ProductVersion') ProductVersion
  , SERVERPROPERTY( 'InstanceName') InstanceName
  , SERVERPROPERTY('InstanceDefaultDataPath') InstanceDefaultDataPath
  , SERVERPROPERTY( 'InstanceDefaultLogPath') InstanceDefaultLogPath
  , DB_NAME() DatabaseName
;
SELECT name FROM msdb.sys.databases;"

$ds = invoke-sqlcmd -ServerInstance $ServerInstance -Query $sql -OutputAs dataset
$ds

As you can see there is a lot of information in the object that gets returned and saved in the $ds variable. However, that still is not the data we are looking for. You may have noticed the top line RemotingFormat: XML. This means I can parse through it quite easily by using the command below which confirms that there is infact 2 tables saved in the object.

$ds.tables.count
2

Now you can see there are 2 tables in this XML and the next command will return the first query. Notice the [0] is the zero based notation for an array so 0 is the first dataset, 1 is the second and so on.

$ds.tables.table[0]

Second query result set

$ds.tables.table[1]

I hope this helps dispel the myth that INVOKE-SQLCMD can't return multiple datasets.