Powershell read xml from Database

hi,

i have tblevents table containing xml data. question how can use powershell pulling xml data (one xml per record) from sql database , save attachments , send via email.

create table [dbo].[tblevents](
 [id] [bigint] identity(1,1) not null primary key,
 [datecreated] [datetime2](7) not null,
 [xml] [xml] null
 )
 

hanks, lan

 

this two-part question first you'll need output each xml document separate file , second want attach file(s) single email. here's code i've tested on machine. you'll need make small edits work in environment:

  $serverinstance = "$env:computername\sql1"  $database = "adventureworkslt"  $query = "select name, catalogdescription saleslt.productmodel catalogdescription not null"    $conn=new-object system.data.sqlclient.sqlconnection  $connectionstring = "server={0};database={1};integrated security=true;" -f $serverinstance,$database  $conn.connectionstring=$connectionstring  $conn.open()  $cmd=new-object system.data.sqlclient.sqlcommand($query,$conn)  $ds=new-object system.data.dataset  $da=new-object system.data.sqlclient.sqldataadapter($cmd)  $null = $da.fill($ds)  $conn.close()    $ds.tables[0] | foreach { $_.catalogdescription | out-file -filepath "./$($_.name).xml"}  get-childitem | select -expandproperty fullname |   send-mailmessage -from "user01@example.com" -to "user02@example.com" -subject "sending attachment" -body "here's xml files." -smtpserver smtp.fabrikam.com    

Windows Server  >  Windows PowerShell