How to use Select-Xml cmdlet to parse XML from external source?

Question

I'm trying to use Select-Xml to parse XML from SQL server. I have a sql query which gives me an XML string ($Query) and I want to sum up all "Users" values from this XML.

My script works perfectly if I use external file for "-Path". But I don't know how to use put XML string into "-Path".

If I run Write-Host $path i'm getting just a "System.Data.DataRow" message

If I run echo $path i'm getting XML string.

$XPath = "//Users" # XML element
$total = 0 # Counter to sum up all Users elements

$Query = "SELECT [LicXML] FROM [SQLSERVER].[dbo].[Conf] WHERE Name='Lic'" # Query to get XML string
$path = Invoke-Sqlcmd -Query $Query -ServerInstance 'SQLSERVER' -Database SQLDB # Request to MSSQL

Select-Xml -Path $path -XPath $Xpath | ForEach-Object { $total += [int] $_.node.InnerXML }; # Parsing
Write-Host "total:" $total # Users sum

I tried different ways but still have no idea. Could you help me please?

Screenshot of the result

UPD. I'm too dumb for this place. Firstly, I must use -Content flag instead of -Path But still dont' know how to put my XML string to the -Content

UPD2. I believe I can use $path = $path | Out-String to push the XML string in my variable. But now I have other problem - how to cut off the header of the query result? Because it's not a valid XML until the header is presented:

Select-Xml : Cannot convert value "
LicXML                                                                                                                                                    
-------                                                                                                                                                    
<License ProductID="ICE"><Data Version="1.0" AuthenticationKeyID="PCApps">...
" to type "System.Xml.XmlDocument"

Then you want to pass it to either `-InputObject` or pipe it: `$path.BigStr |Select-Xml ...` — Mathias R. Jessen, May 18 '22 at 13:59
@MathiasR.Jessen Actually I'm stuck with removing a header :( could you help with that? — Steamy Mr., May 18 '22 at 14:19
Assuming `Invoke-SqlCmd` returns a datarow with a `LicXML` column, you should be able to get the raw string value by referencing `$path["LicXML"]` (I got confused by the "BigStr1" column header in the error message, did you mannualy change it?) — Mathias R. Jessen, May 18 '22 at 14:27
Conversely, if the column name is indeed `BigStr1`: `$path["BigStr1"] |Select-Xml ...` — Mathias R. Jessen, May 18 '22 at 14:44

score 0 · Answer 1 · answered May 18 '22 at 17:18

Finally:

$XPath = "//Users" #XML Value to find
$total = 0 #Counter to sum up Users
$Query = "SELECT [LicXML] FROM [SQLSERVER].[dbo].[Conf] WHERE Name='Lic'" #Query to gather raw XML string
$path = Invoke-Sqlcmd -Query $Query -ServerInstance SQLSERVER #Sending a query
$path["LicXML"] | Select-Xml -XPath $Xpath | ForEach-Object { $total += [int] $_.node.InnerXML }; #Black magic, XML parsing and counting a sum

How to use Select-Xml cmdlet to parse XML from external source?

1 Answers1