Invoke-ASCmd XMLA Results to storage results in Azure Data Lake with either .csv or .txt format

Question

I've built an Azure Powershell runbook (5.0) to run a MDX query against a SSAS server , the query is just to get the partition names

SELECT 
 [Name]
FROM $SYSTEM.TMSCHEMA_PARTITIONS 
WHERE [Name] <>'Partition'

If I run it in SSMS I get the desired results

The problem comes when I run the query using the powershell runbook because the results are basically an XMLA output

What I need is the Azure powershell runbook to extract just that column from the $Results variable and then create either a .txt file or .csv in the Azure data lake storage V2 with that data ( like the SSMS). That file will be consumed later by Data Factory.

This is the full script:

Param
(
  [Parameter (Mandatory= $true)]
  [String] $Query,
  [Parameter (Mandatory= $true)]
  [String] $OperationType,
  [Parameter (Mandatory= $true)]
  [String] $ServerName,
  [Parameter (Mandatory= $true)]
  [String] $DatabaseName,
  [Parameter (Mandatory= $true)]
  [String] $SecretName

)

$cred = Get-AutomationPSCredential -Name $SecretName

$Results = Invoke-ASCmd -Server $ServerName -Database $DatabaseName -Credential $cred -Query $Query

Write-Output $Results

Answer 1

I have reproduced in my environment and got expected results as below:

SSMS:

SELECT [Name] FROM $SYSTEM.TMSCHEMA_PARTITIONS

NOTE:

Your $Results is taking the output as string, you should be converting it to Xml using [XML] in PowerShell script.

Then use below code instead of your code :

$Results = [XML] (Invoke-ASCmd -Server $ServerName -Database $DatabaseName -Credential $cred -Query $Query)

Now use below code:

Write-Output  $Results.return.root.row.Name

Output:

You need to incorporate the above 2 codes into your code.After that you need to send the output to your requirement.