Post reply

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

  • August 1, 2013 at 1:08 pm

    #297150

    I tried to strip down this example to make it as terse as possible so it would be easy to read\answer.

    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 an example of some xml data (located in C:\test\test.xml) I want to insert:

    <!-- Edited by XMLSpy® -->

    <note>

    <to>Tove</to>

    <from>Jani</from>

    <heading>Reminder</heading>

    <body>Don't forget me this weekend!</body>

    </note>

    Here is the script I am using to try and insert it:

    $SQLDBName = 'test'

    $SQLServer = 'myserver,1433'

    #Open connection to sql DB

    $connectionString = "Server = $SQLServer;Integrated Security=true;Initial Catalog=$SQLDBName;"

    #$connectionString = "Server = $SQLServer;Data Source=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 @("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("EventLogXML") = [xml](cat C:\test\test.xml)

    $dt.Rows.Add($row) #add row to table

    #insert into DB and close connection

    $SQLConnection.WriteToServer($dt)

    $SQLConnection.close()

    Running this script I get the following error:

    Exception calling "WriteToServer" with "1" argument(s): "Specified cast is not valid."

  • August 1, 2013 at 2:37 pm

    #1637957

    Got answer:

    http://stackoverflow.com/questions/17999063/insert-xml-data-into-sql-server-via-sqlbulkcopy-with-powershell-casting-error

    "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]"

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply