Powershell SQL query results convertto-XML

Question

I have been having quite the time trying to figure this out. Let me try to explain what I am trying to accomplish, I hope i can be clear enough.

I am sending two queries to an MSSQL database and receiving them back. The below code works perfect, however I would like to manipulate the format of the XML a bit before it writes to the XML file. I currently get 3 columns (serviceGroupName, numAccounts, numDevices) I would like to accomplish 1 of 2 things:

1) Add a new column named "ReportType" and have it fill in "Monthly" Or "Total" depending on if it is pass 1 or 2 of the foreach loop (SQLQuery1 is Monthly report, and SQLQuery2 is Total number since inception)

2) Create a new PSObject and have it fill in the appropriate information such as the data it receives back (serviceGroupName, numAccounts, numDevices)

Below is my current code. As i mentioned it does work and it generated an XML but i would like to add some more information before the pipe to ConvertTo-XML if possible.

### Dates to use
$Date = (Get-Date -f MM-dd-yyyy)
$FDoTM = ((Get-Date -Day 01).AddMonths(0)).AddDays(0)
$LDo2PM = ((Get-Date -Day 01).AddMonths(-1)).AddDays(-1)
$TempDir = "C:\Temp"
$WebDir =     @("\\x.x.x.x\c$\inetpub\wwwroot\Reports\Accounts","\\x.x.x.x\c$\inetpub\wwwroot\Reports\Accounts")

### Something

$OutputXML = "$Date-Monthly-AccountReport.xml"

### Connection settings, uses windows authentication

$DBServer = "OMMITED"
$databasename = "OMMITED"
$Connection = new-object system.data.sqlclient.sqlconnection #Set new object to connect to sql database
$Connection.ConnectionString ="server=$DBServer;database=$databasename;trusted_connection=True" # Connectiongstring setting for local machine database with window authentication
Write-host "Connection Information:"  -foregroundcolor yellow -backgroundcolor black
$Connection #List connection information


### Connect to Database and Run Query

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand #setting object to use sql commands

$OutputHeader1 = "This Month's counts"
$SqlQuery1 = @"

SET NOCOUNT ON;

WITH AccountDeviceStats(serviceGroupName,numAccounts,numDevices)
AS
(
    SELECT svg.name,COUNT(acct.serviceGroupId) as Accounts, NULL FROM bm_account acct WITH     (NOLOCK)
    INNER JOIN bm_servicegroup svg WITH (NOLOCK) ON svg.servicegroupId = acct.serviceGroupId

    where acct.CreateStamp between '$($LDo2PM)' and '$($FDoTM)'
GROUP BY acct.serviceGroupId,svg.name
UNION ALL
SELECT svg.name, NULL, COUNT(device.serviceGroupId) as Devices FROM bm_device device WITH (NOLOCK)
INNER JOIN bm_servicegroup svg WITH (NOLOCK) ON svg.servicegroupId = device.serviceGroupId, bm_account acct

where device.accountID=acct.accountId and acct.CreateStamp between '$($LDo2PM)' and '$($FDoTM)'
GROUP BY device.serviceGroupId,svg.name
)
SELECT ad1.serviceGroupName,ad1.numAccounts,ad2.numDevices FROM AccountDeviceStats ad1
INNER JOIN AccountDeviceStats ad2 ON ad1.serviceGroupName = ad2.serviceGroupName
WHERE ad1.numAccounts IS NOT NULL AND ad2.numDevices IS NOT NULL
ORDER BY numAccounts DESC,numDevices DESC
"@

$OutputHeader2 = "Total Counts"
$SqlQuery2 = @"

SET NOCOUNT ON;

WITH AccountDeviceStats(serviceGroupName,numAccounts,numDevices)
AS
(
SELECT svg.name,COUNT(acct.serviceGroupId) as Accounts, NULL FROM bm_account acct WITH     (NOLOCK)
INNER JOIN bm_servicegroup svg WITH (NOLOCK) ON svg.servicegroupId = acct.serviceGroupId

where acct.CreateStamp < '12-31-2099'
GROUP BY acct.serviceGroupId,svg.name
UNION ALL
SELECT svg.name, NULL, COUNT(device.serviceGroupId) as Devices FROM bm_device device WITH (NOLOCK)
INNER JOIN bm_servicegroup svg WITH (NOLOCK) ON svg.servicegroupId = device.serviceGroupId, bm_account acct

where device.accountID=acct.accountId and acct.CreateStamp < '12-31-2099'
GROUP BY device.serviceGroupId,svg.name
)
SELECT ad1.serviceGroupName,ad1.numAccounts,ad2.numDevices FROM AccountDeviceStats ad1
INNER JOIN AccountDeviceStats ad2 ON ad1.serviceGroupName = ad2.serviceGroupName
WHERE ad1.numAccounts IS NOT NULL AND ad2.numDevices IS NOT NULL
ORDER BY numAccounts DESC,numDevices DESC
"@

$sqlQueries = @($SqlQuery1, $SqlQuery2)

$Results = @()

Foreach ($Query in $sqlQueries){
    $Connection.open()
    Write-host "Connection to database successful." -foregroundcolor green -backgroundcolor black
    $SqlCmd.CommandText = $Query
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlCmd.Connection = $Connection
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $Connection.Close()

$Results += $DataSet.Tables[0]

($Results | ConvertTo-XML -NoTypeInformation).Save("$TempDir\$OutputXML")
}

if ((Get-ChildItem $TempDir -filter "$Date-*.xml").count -gt 0){    
Foreach ($file in (Get-ChildItem $TempDir -filter "$Date-*.xml" -recurse)){
    Foreach ($webserver in $WebDir){
        Copy-Item $file.fullname "$webserver\$file" -force
        }
    Remove-Item $file.fullname -force
    }
}

Here is the output formatting of the XML

<?xml version="1.0"?>
<Objects>
  <Object>
    <Property Name="serviceGroupName">ServiceGroup1</Property>
    <Property Name="numAccounts">15</Property>
    <Property Name="numDevices">28</Property>
    <Property Name="RowError" />
    <Property Name="RowState">Unchanged</Property>
    <Property Name="Table">
      <Property>System.Data.DataRow</Property>
    </Property>
    <Property Name="ItemArray">
      <Property>ServiceGroup1</Property>
      <Property>15</Property>
      <Property>28</Property>
    </Property>
    <Property Name="HasErrors">False</Property>
  </Object>
  <Object>
    <Property Name="serviceGroupName">ServiceGroup1</Property>
    <Property Name="numAccounts">45</Property>
    <Property Name="numDevices">69</Property>
    <Property Name="RowError" />
    <Property Name="RowState">Unchanged</Property>
    <Property Name="Table">
      <Property>System.Data.DataRow</Property>
    </Property>
    <Property Name="ItemArray">
  <Property>ServiceGroup1</Property>
  <Property>45</Property>
  <Property>69</Property>
</Property>
<Property Name="HasErrors">False</Property>

And one last thing. If it's possible to remove the excess bloat from the XML, as you can see it doubles the data output because it creates a node named ItemArray with all of the same information.

I hope this is easy enough to understand. If you need any more information, please let me know. And thank you in advance for any and all help.

ljh · Accepted Answer · 2013-04-04 05:35:23Z

I think all you need to do is to update your two T-sql queries within the powershell script. First one, add code like following:


...., "Monthly" as ReportType FROM AccountDeviceStats ad1...

Second one, add code like following:


...., "Total" as ReportType FROM AccountDeviceStats ad1...


### Dates to use
$Date = (Get-Date -f MM-dd-yyyy)
$FDoTM = ((Get-Date -Day 01).AddMonths(0)).AddDays(0)
$LDo2PM = ((Get-Date -Day 01).AddMonths(-1)).AddDays(-1)
$TempDir = "C:\Temp"
$WebDir =     @("\\x.x.x.x\c$\inetpub\wwwroot\Reports\Accounts","\\x.x.x.x\c$\inetpub\wwwroot\Reports\Accounts")

### Something

$OutputXML = "$Date-Monthly-AccountReport.xml"

### Connection settings, uses windows authentication

$DBServer = "OMMITED"
$databasename = "OMMITED"
$Connection = new-object system.data.sqlclient.sqlconnection #Set new object to connect to sql database
$Connection.ConnectionString ="server=$DBServer;database=$databasename;trusted_connection=True" # Connectiongstring setting for local machine database with window authentication
Write-host "Connection Information:"  -foregroundcolor yellow -backgroundcolor black
$Connection #List connection information


### Connect to Database and Run Query

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand #setting object to use sql commands

$OutputHeader1 = "This Month's counts"
$SqlQuery1 = @"

SET NOCOUNT ON;

WITH AccountDeviceStats(serviceGroupName,numAccounts,numDevices)
AS
(
    SELECT svg.name,COUNT(acct.serviceGroupId) as Accounts, NULL FROM bm_account acct WITH     (NOLOCK)
    INNER JOIN bm_servicegroup svg WITH (NOLOCK) ON svg.servicegroupId = acct.serviceGroupId

    where acct.CreateStamp between '$($LDo2PM)' and '$($FDoTM)'
GROUP BY acct.serviceGroupId,svg.name
UNION ALL
SELECT svg.name, NULL, COUNT(device.serviceGroupId) as Devices FROM bm_device device WITH (NOLOCK)
INNER JOIN bm_servicegroup svg WITH (NOLOCK) ON svg.servicegroupId = device.serviceGroupId, bm_account acct

where device.accountID=acct.accountId and acct.CreateStamp between '$($LDo2PM)' and '$($FDoTM)'
GROUP BY device.serviceGroupId,svg.name
)
SELECT ad1.serviceGroupName,ad1.numAccounts,ad2.numDevices, ""Monthly"" as ReportType  FROM AccountDeviceStats ad1
INNER JOIN AccountDeviceStats ad2 ON ad1.serviceGroupName = ad2.serviceGroupName
WHERE ad1.numAccounts IS NOT NULL AND ad2.numDevices IS NOT NULL
ORDER BY numAccounts DESC,numDevices DESC
"@

$OutputHeader2 = "Total Counts"
$SqlQuery2 = @"

SET NOCOUNT ON;

WITH AccountDeviceStats(serviceGroupName,numAccounts,numDevices)
AS
(
SELECT svg.name,COUNT(acct.serviceGroupId) as Accounts, NULL FROM bm_account acct WITH     (NOLOCK)
INNER JOIN bm_servicegroup svg WITH (NOLOCK) ON svg.servicegroupId = acct.serviceGroupId

where acct.CreateStamp < '12-31-2099'
GROUP BY acct.serviceGroupId,svg.name
UNION ALL
SELECT svg.name, NULL, COUNT(device.serviceGroupId) as Devices FROM bm_device device WITH (NOLOCK)
INNER JOIN bm_servicegroup svg WITH (NOLOCK) ON svg.servicegroupId = device.serviceGroupId, bm_account acct

where device.accountID=acct.accountId and acct.CreateStamp < '12-31-2099'
GROUP BY device.serviceGroupId,svg.name
)
SELECT ad1.serviceGroupName,ad1.numAccounts,ad2.numDevices, ""Total"" as ReportType FROM AccountDeviceStats ad1
INNER JOIN AccountDeviceStats ad2 ON ad1.serviceGroupName = ad2.serviceGroupName
WHERE ad1.numAccounts IS NOT NULL AND ad2.numDevices IS NOT NULL
ORDER BY numAccounts DESC,numDevices DESC
"@

$sqlQueries = @($SqlQuery1, $SqlQuery2)

$Results = @()

Foreach ($Query in $sqlQueries){
    $Connection.open()
    Write-host "Connection to database successful." -foregroundcolor green -backgroundcolor black
    $SqlCmd.CommandText = $Query
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlCmd.Connection = $Connection
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $Connection.Close()

$Results += $DataSet.Tables[0]

($Results | ConvertTo-XML -NoTypeInformation).Save("$TempDir\$OutputXML")
}

if ((Get-ChildItem $TempDir -filter "$Date-*.xml").count -gt 0){    
Foreach ($file in (Get-ChildItem $TempDir -filter "$Date-*.xml" -recurse)){
    Foreach ($webserver in $WebDir){
        Copy-Item $file.fullname "$webserver\$file" -force
        }
    Remove-Item $file.fullname -force
    }
}

Hmm.. That's didn't seem to work, it through some errors when I tried to run it. It said "Invalid column name 'Monthly'." I possibly added it in the wrong spot, I tried 2 places I tried to add it after the Where and before the Order statements. And then I tried to add just "Monthly" as ReportType in the initial FROM statement where I grab the other column names in the beginning.
Update the answer, add full code, you check if you already add the constant column in the same location.
That worked great. Thank you so much. Dunno If I managed to mistype something when I manually added it.

Chloe · Accepted Answer · 2016-04-01 16:25:38Z

The original question asked how to remove the bloat from the XML as well. I was looking for a solution where the XML that I was generating from the SQL results had to be in an absolute specific format with the correct tags and everything in place. What I discovered was that once you have your dataset object ($DataSet) then if you look to see what methods and properties are available to it, ($DataSet | gm) then one of them is GetXML().

This automatically formats your SQL output such that each returned column (or column alias) is returned as a separate tag (although note, it does not generate an empty tag for a null value) so in this instance if you use $DataSet.GetXML() I would have expected to see output something along the lines of

 <NewDataSet>
  <Table>
    <serviceGroupName>ServiceGroup1</serviceGroupName>
    <numAccounts>15</numAccounts>
    <numDevices>28</numDevices>
  </Table>
</NewDataSet>

so no bloat!

As this is just a series of strings, you can then do things like ($Dataset.GetXML()).Replace('NewDataSet','OuterTag').Replace('Table','InnerTag') to give better labels to the XML. Once you are happy with this you can output

SET-CONTENT -PATH $xmlfilename -VALUE '<?xml version="1.0" ?>'

or some such to a file and then append the output from your GetXML() method so you have a much neater formatted piece of XML!

($DataSet.GetXML()).Replace('NewDataSet','OuterTagName').Replace('Table','InnerTagName') | ADD-CONTENT -PATH $xmlfilename

Collectives™ on Stack Overflow

Powershell SQL query results convertto-XML

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related