Alyanto
asked on
Inserting XML into SQL Server Table
I have a function as below however I now need to write a similar one that inserts the XML into a SQL server 2008 table. How is this achieved?
Public Shared Function WriteXMLToDisk(ByVal Despatches As Generic.List(Of XMLDespatch), ByVal FilePath As String) As Boolean
'XElement xmlCustomers = new XElement(" customers", from c in customers where c.Country = = Countries.Italy select new XElement(" customer", new XElement(" name", c.Name), new XElement(" city", c.City), new XElement(" country", c.Country)));
Try
Dim XMLDespatches As XElement = New XElement("Despatches", From d In Despatches _
Select New XElement("Despatch", _
New XAttribute("LongCode", d.LongCode), _
New XAttribute("Memo", d.Memo), _
New XAttribute("OtherBranch", d.OtherBranch), _
New XAttribute("TransactionDate", d.TransactionDate), _
New XAttribute("TransactionType", d.TransactionType), _
New XAttribute("Units", d.Units.ToString)))
If Not Directory.Exists(CommonAttributes.DespatchesPath) Then
Directory.CreateDirectory(CommonAttributes.DespatchesPath)
End If
If File.Exists(FilePath) Then
File.Delete(FilePath)
End If
XMLDespatches.Save(FilePath, SaveOptions.None)
Return True
Catch ex As Exception
Return False
End Try
End Function
ASKER
So what you are saying is that it is no diferent to anyother database transaction.
Declare a SQLConnection, Declare a SQLSQLCommand etc... and then run an insert query or Stored Proc. Nothing special, no specific way of couching the parameters etc?
Declare a SQLConnection, Declare a SQLSQLCommand etc... and then run an insert query or Stored Proc. Nothing special, no specific way of couching the parameters etc?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers, soled the problem for me
to save "xml" into a database, you need the following scripts:
* build the XML (same as above, except that you don't .Save to a file, but I presume the object has a property returning the full xml), so no issue here?
* connect to the database, which has the table with the xml column
* performing the INSERT / UPDATE using the above xml string