Insert xml data into SQL server via SqlBulkCopy with powershell (casting error)

.net powershell sqlbulkcopy sql-server xml

Question

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,
)

Accepted Answer

I often use string data types for DataColumns while keeping my SQL Server table an XML datatype. This works fine for me.

Instead of converting your xml file to xml just use (cat C:\test\4625_forForums.xml -raw) And define your [System.Xml.XmlNode] as [System.String]



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why