Microsoft Windows PowerShell and SQL Server 2005 SMO – Part 11

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"



Fig 1.2

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]



Fig 1.3

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



Fig 1.4

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"



Fig 1.5

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]



Fig 1.6

Content
of the test.xml file


– <Objs Version=”1.1″ >
– <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>



Fig 1.7

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

Latest Articles