I'm getting a "cast not valid" error when trying to insert a data table containing xml using sqlbulckcopy.
I cast the DataColumn that will hold the xml data to type System.Xml.XmlNode is that incorrect? Looking here it just says the .NET Framework type is xml.
Here is an example of the xml I want to insert:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Microsoft-Windows-Security-Auditing" Guid="{54849625-5478-4994-a5ba-3e3b0328c30d}"/>
<EventID>4625</EventID>
<Version>0</Version>
<Level>0</Level>
<Task>12544</Task>
<Opcode>0</Opcode>
<Keywords>0x8010000000000000</Keywords>
<TimeCreated SystemTime="2013-07-19T19:27:14.515Z"/>
<EventRecordID>39751424</EventRecordID>
<Correlation/>
<Execution ProcessID="568" ThreadID="1608"/>
<Channel>Security</Channel>
<Computer>myserver.mydom.com</Computer>
<Security/>
</System>
<EventData>
<Data Name="SubjectUserSid">mysid</Data>
<Data Name="SubjectUserName">myuser</Data>
<Data Name="SubjectDomainName">mydom</Data>
<Data Name="SubjectLogonId">0x5ca73f</Data>
<Data Name="TargetUserSid">S-1-0-0</Data>
<Data Name="TargetUserName">myuser2</Data>
<Data Name="TargetDomainName">mydom</Data>
<Data Name="Status">0xc000006d</Data>
<Data Name="FailureReason">%%2313</Data>
<Data Name="SubStatus">0xc000006a</Data>
<Data Name="LogonType">2</Data>
<Data Name="LogonProcessName">seclogo</Data>
<Data Name="AuthenticationPackageName">Negotiate</Data>
<Data Name="WorkstationName">myserver</Data>
<Data Name="TransmittedServices">-</Data>
<Data Name="LmPackageName">-</Data>
<Data Name="KeyLength">0</Data>
<Data Name="ProcessId">0x3e8</Data>
<Data Name="ProcessName">C:\Windows\System32\svchost.exe</Data>
<Data Name="IpAddress">::1</Data>
<Data Name="IpPort">0</Data>
</EventData>
</Event>
I get the following error when trying to insert data into sql via the following script:
$SQLDBName = 'test'
$SQLServer = 'mysrv,1433'
#Open connection to sql DB
$connectionString = "Server = $SQLServer;Integrated Security=true;Initial Catalog=$SQLDBName;"
$SQLConnection = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$SQLConnection.DestinationTableName = "ForwardedEvents"
#create columns for datatable and set their types
$columns = @()
$columns += #Order MUST match that of the DB
(New-Object System.Data.DataColumn -ArgumentList @("SystemTime" , [System.string] )),
(New-Object System.Data.DataColumn -ArgumentList @("EventLogXML" , [System.Xml.XmlNode] ))
#build datatable for bulk insert
$dt = New-Object System.Data.DataTable
$columns | %{$dt.Columns.add($_) | Out-Null}
$row = $dt.NewRow() #Create row
$row.Item("SystemTime") = "This string get inserted fine"
$row.Item("EventLogXML") = [xml](cat C:\test\4625_forForums.xml)
$dt.Rows.Add($row) #add row to table
#insert into DB and close connection
$SQLConnection.WriteToServer($dt)
$SQLConnection.close()
Error: Exception calling "WriteToServer" with "1" argument(s): "Specified cast is not valid."
Really appreciate any assistance, thanks in advance.
edit: here is the datatable:
CREATE TABLE ForwardedEvents
(
SystemTime NVarChar(200) NOT NULL,
EventLogXML XML NOT NULL,
)