Unable to Parse output from the ExecuteSQL Verb in PowerShell

I've assigned the output of the ExecuteSQL verb in Powershell (using the Invoke-SwisVerb cmdlet) to a variable, but I can't figure out how to parse the results. It says it is in XML format but the only properties that the XML object has is diffgram, schema, and xmlns. None of which have the results of the SQL query in them. If I view the .InnerText of the object I see all of my results crammed together into one massive string. No property names, no separator that I can split on. Nothing... I've searched all over google and Thwack and the few examples of ExecuteSQL I've found have been people using it to perform Inserts or to perform a single count, meaning they weren't expecting to get back an object with a bunch of results.

The SQL query I'm running is this (which I wouldn't have to do if NCM just easily showed us this data by default):

SELECT
  n.NodeCaption
  ,n.CoreNodeID
  ,ISNULL(n.NCM_Username, 'EMPTY') AS OldProperty
  ,CASE
WHEN np.ConnectionProfile = 0 THEN ngs.SettingValue
ELSE ncp.Username
END AS NewPropertyEncrypted
  ,np.ConnectionProfile
  FROM NCM_Nodes n
  JOIN NCM_NodeProperties np ON n.CoreNodeID = np.CoreNodeID
  LEFT OUTER JOIN NCM_ConnectionProfiles ncp ON np.ConnectionProfile = ncp.ID
  LEFT OUTER JOIN NCM_GlobalSettings ngs ON ngs.SettingName = 'GlobalUsername'

**NOTE: The query above won't work if you don't have a Node custom property called NCM_Username. Just remove the following line from the query so that it will run in your SQL DB:

,ISNULL(n.NCM_Username, 'EMPTY') AS OldProperty

I'm not good with XML, I'll admit, but I have parsed it several times in PowerShell before and always been able to access the properties and their values pretty easily. This time I'm stumped. What am I doing wrong tdanner?

Find more posts tagged with

swis

SWQL

xml

parseing

PowerShell

executesql

Accepted answers

tdanner

It is a serialized .NET DataTable, so the XML is a little weird. You can either deserialize it back to a DataTable using DataContractSerializer, but this is a bit awkward to set up in PowerShell. You can also just access the data directly. If $xml holds the value returned from Invoke-SwisVerb, the collection of results is at $xml.diffgram.DocumentElement.ExecuteSQLResults.

A useful trick with XML in PowerShell: to get PowerShell to just dump the whole XML so you can see it all at once, use $xml.OuterXml.

All comments

tdanner

A useful trick with XML in PowerShell: to get PowerShell to just dump the whole XML so you can see it all at once, use $xml.OuterXml.

xtraspecialj

This is super sweet. I feel so inadequate saying this yet again, but thank you so much tdanner for another quick, accurate, and extremely helpful answer. It works like a charm!!

By the way, I used the $xml.diffgram.DocumentElement.ExecuteSQLResults method that he suggested as that was the simplest way.