apr 8 blog

PowerShell and MSMQ – Query SQL database and output DataSet to XML

Use the script below to Query a SQL database with powershell and output the data in XML format:



$SQLServer = "ANJEP-SQL" #use ServerInstance for named SQL instances!
$SQLDBName = "Fakedemodata"
$SqlQuery =

	"
	SELECT TOP 10
		(SELECT TOP 1
			*
		FROM
			FakeNameGenerator AS SUB
		WHERE
			SUB.Number = MAIN.Number
		FOR XML RAW
			('Contact'), root('Contacts'), ELEMENTS XSINIL
			--, XMLSCHEMA //Enable to get the XSD schaema
		) AS XML
		FROM
			FakeNameGenerator AS MAIN
	ORDER BY
	MAIN.Number

	"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

#clear

#$DataSet.Tables[0]

foreach ($row in $DataSet.Tables[0].Rows)

{

write-host $row[0].ToString()
write-host

}

Share on FacebookTweet about this on TwitterShare on LinkedInShare on Google+