SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
fakemailred888
fakemailred888
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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."

fakemailred888
fakemailred888
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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]"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search