title: Powershell SQL query results convertto-XML
body: <p>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. </p>
  
  <p>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:</p>
  
  <p>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)</p>
  
  <p>2) Create a new PSObject and have it fill in the appropriate information such as the data it receives back (serviceGroupName, numAccounts, numDevices)</p>
  
  <p>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.</p>
  
  <pre><code>### 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 &lt; '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 &lt; '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
      }
  }
  </code></pre>
  
  <p>Here is the output formatting of the XML</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
  &lt;Objects&gt;
    &lt;Object&gt;
      &lt;Property Name="serviceGroupName"&gt;ServiceGroup1&lt;/Property&gt;
      &lt;Property Name="numAccounts"&gt;15&lt;/Property&gt;
      &lt;Property Name="numDevices"&gt;28&lt;/Property&gt;
      &lt;Property Name="RowError" /&gt;
      &lt;Property Name="RowState"&gt;Unchanged&lt;/Property&gt;
      &lt;Property Name="Table"&gt;
        &lt;Property&gt;System.Data.DataRow&lt;/Property&gt;
      &lt;/Property&gt;
      &lt;Property Name="ItemArray"&gt;
        &lt;Property&gt;ServiceGroup1&lt;/Property&gt;
        &lt;Property&gt;15&lt;/Property&gt;
        &lt;Property&gt;28&lt;/Property&gt;
      &lt;/Property&gt;
      &lt;Property Name="HasErrors"&gt;False&lt;/Property&gt;
    &lt;/Object&gt;
    &lt;Object&gt;
      &lt;Property Name="serviceGroupName"&gt;ServiceGroup1&lt;/Property&gt;
      &lt;Property Name="numAccounts"&gt;45&lt;/Property&gt;
      &lt;Property Name="numDevices"&gt;69&lt;/Property&gt;
      &lt;Property Name="RowError" /&gt;
      &lt;Property Name="RowState"&gt;Unchanged&lt;/Property&gt;
      &lt;Property Name="Table"&gt;
        &lt;Property&gt;System.Data.DataRow&lt;/Property&gt;
      &lt;/Property&gt;
      &lt;Property Name="ItemArray"&gt;
    &lt;Property&gt;ServiceGroup1&lt;/Property&gt;
    &lt;Property&gt;45&lt;/Property&gt;
    &lt;Property&gt;69&lt;/Property&gt;
  &lt;/Property&gt;
  &lt;Property Name="HasErrors"&gt;False&lt;/Property&gt;
  </code></pre>
  
  <p>
  </p>
  
  <p>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.</p>
  
  <p>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.</p>