使用powershell通过SqlBulkCopy将xml数据插入SQL server(强制转换错误)

Question

当我尝试使用sqlbulckcopy插入包含xml的数据表时，收到一个"强制转换无效"错误。

我将保存xml数据的DataColumn强制转换为System.xml.XmlNode类型，这不正确吗？从这里看，它只是说.NET Framework的类型是xml。

下面是我想要插入的xml示例：

<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:WindowsSystem32svchost.exe</Data>
        <Data Name="IpAddress">::1</Data>
        <Data Name="IpPort">0</Data>
    </EventData>
</Event>

当我试图通过以下脚本将数据插入sql时，我得到了以下错误：

$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:test4625_forForums.xml)
$dt.Rows.Add($row) #add row to table
#insert into DB and close connection
$SQLConnection.WriteToServer($dt)
$SQLConnection.close()

错误：使用"1"参数调用"WriteToServer"时发生异常："指定的强制转换无效。"

非常感谢任何帮助，提前谢谢。

编辑：这是数据表：

CREATE TABLE ForwardedEvents
(
    SystemTime              NVarChar(200)     NOT NULL,
    EventLogXML             XML               NOT NULL,
)

Answer 1

我经常为DataColumns使用字符串数据类型，同时将SQL Server表保持为XML数据类型。这对我来说很好。

不用将xml文件转换为xml，只需使用（cat C:\test\4625_forForums.xml-raw）并将[System.Xml.XmlNode]定义为[System.String]