Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11

November 2, 2007

Export output to XML

Part 1 and Part 2 of this series discussed Power Shell installation and simple SMO, WMI cmdlets. Part 3 covered how to script PowerShell and connect to SQL Server. Part 4 explained how to use a PowerShell script to loop through the content of a file and connect to different servers. Part 5 examined creating a SQL Server database using PowerShell and SMO. Part 6 talked about backing up a SQL Server database using PowerShell and SMO. Part 7 illustrated how to list all of the objects in a database and part 8 demonstrated how to list all of the properties of the objects in a database using PowerShell and SMO. Part 9 of this article series illustrated how to use PowerShell and SMO to generate a script for database and tables. Part 10 illustrated creating the PowerShell script to generate a script for the database and tables.

This installment of the series illustrates how to use PowerShell cmdlets in conjunction with the SQL Server client and output redirection to export to a text file or XML file.

Let us assume we want to query any SQL Server table using transact sql and store the output in text format or in XML format. Using PowerShell cmdlets, SQL Server client connection and output redirection, this can be achieved very easily.

Let us create c:\ps\output.ps1 as shown below. [Refer Fig 1.1]

param 
(
  [string] $SQLServer,
  [string] $Database,
  [string] $outputType,
  [string] $filename,
  [string] $Query
)

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString =  
"Server=$SQLSERVER;Database=$DATABASE;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

if ($outputType  -eq "Text") 
{
$DataSet.Tables[0] | format-table -auto > $filename
}

if ($outputType  -eq "xml") 
{
$DataSet.Tables[0] |Export-Clixml $filename
}

This script can be executed as shown below. [Refer Fig 1.2]

./output "HOME\SQLEXPRESS" "VixiaTrack" "TEXT" "c:\test.txt" "Select dbid,name from sys.sysdatabases"

Parameters explained:

• output is actually the output.ps1 script in the folder c:\ps
• HOME is the hostname
• SQLEXPRESS is the sql server instance name on the host HOME
• VixiaTrack is the database name that resides in the SQLEXPRESS instance

TEXT is the ouput format desired. It could be TEXT or XML

• C:\test.txt is the filename and its location
• Select dbid,name from sys.sysdatabases is the actual Transact SQL query executed against the database

When the PowerShell script is executed, it queries the database and saves the output to a text file that has been passed as parameter. [Refer Fig 1.3 and Fig 1.4]

Content of the test.txt file

dbid name          
---- ----          
   1 master        
   2 tempdb        
   3 model         
   4 msdb          
   5 test          
   6 VixiaTrack    
   7 XMLTest       
   8 admin         
   9 AdventureWorks

The same PowerShell script can be executed using XML as the parameter in order to generate the result in XML format.

This script can be executed as shown below. [Refer Fig 1.5]

./output "HOME\SQLEXPRESS" "VixiaTrack" "XML" "c:\test.xml" "Select dbid,name from sys.sysdatabases"

Parameters explained:

• output is actually the output.ps1 script in the folder c:\ps
• HOME is the hostname
• SQLEXPRESS is the sql server instance name on the host HOME
• VixiaTrack is the database name that resides in SQLEXPRESS instance

XML is the ouput format desired. It could be TEXT or XML.

• C:\test.txt is the filename and its location.
• Select dbid,name from sys.sysdatabases is the actual Transact SQL query executed against the database

When the PowerShell script is executed, it queries the database and saves the output to a XML file that has been passed as parameter. [Refer Fig 1.6 and Fig 1.7]

Content of the test.xml file

- <Objs Version="1.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
- <Obj RefId="RefId-0">
- <TN RefId="RefId-0">
  <T>System.Data.DataRow</T> 
  <T>System.Object</T> 
  </TN>
- <Props>
  <I16 N="dbid">1</I16> 
  <S N="name">master</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">2</I16> 
  <S N="name">tempdb</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">3</I16> 
  <S N="name">model</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">4</I16> 
  <S N="name">msdb</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">5</I16> 
  <S N="name">test</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">6</I16> 
  <S N="name">VixiaTrack</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">7</I16> 
  <S N="name">XMLTest</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">8</I16> 
  <S N="name">admin</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">9</I16> 
  <S N="name">AdventureWorks</S> 
  </Props>
  </Obj>
  </Objs>

Conclusion

Part 11 of this article series illustrated how to use PowerShell cmdlets in conjunction with the SQL Server client and output redirection to export to a text file or XML file.

» See All Articles by Columnist MAK