Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert xml data into SQL server via SqlBulkCopy with powershell (casting error) Expand / Collapse
Author
Message
Posted Thursday, August 01, 2013 1:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 07, 2013 1:27 PM
Points: 2, Visits: 8
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."
Post #1480110
Posted Thursday, August 01, 2013 2:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 07, 2013 1:27 PM
Points: 2, Visits: 8
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]"
Post #1480143
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse