Solved

Inserting XML into SQL Server Table

Posted on 2014-04-09
4
256 Views
Last Modified: 2014-04-11
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

Open in new window

0
Comment
Question by:Alyanto
  • 2
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39989272
can you please clarify what exactly the issue is?
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
0
 
LVL 1

Author Comment

by:Alyanto
ID: 39990264
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?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39990726
exactly. xml is for a database, behind the hoods, nothing else than text, following some rules about the tags, but for your code, still is just a plain text string.
0
 
LVL 1

Author Closing Comment

by:Alyanto
ID: 39993807
Cheers, soled the problem for me
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question