Visual Basic SQL to XML

Introduction

This is a basic program to turn an SQL file into an XML file, then use the XML file to make an XDocument and run a LINQ Query against this.

Background

I was shown some code that produces XML for another process and I wanted to replicate what was done.

Using the Code

Import List

System.Data
System.Data.SqlClient
System.Xml
System.Linq

SQL Portion

1. For this, all one needs is to open a new Windows Form in VB and on the form place a Button and a RichTextBox.
2. Create the necessary steps to connect to an SQL database.
   • Please see these links below for connection string and SQLDataAdapter specifics:
     • SQLDataAdapter
       https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter(v=vs.110).aspx
     • SQLConnection Class
       https://msdn.microsoft.com/en-us/library/System.Data.SqlClient.SqlConnection(v=vs.110).aspx
3. After connecting to the database and running the query. The query I chose to use was a basic select * from a table within the database. The DataTable is filled by using the SQLDataAdapter Fill method.
4. For XML, it is imperative the Data Table has a name as this name is used to name the main node in the XML. The main Element uses this name as its name throughout the XML.

   Dim DA As New SqlDataAdapter()
   Dim cmd As New SqlCommand
   Dim dt As New System.Data.DataTable()
   Dim connex As New SqlConnection
   connex.ConnectionString = "SERVER=YourServer;Database=YourDB;Integrated Security=True;"
   cmd.Connection = connex
   cmd.CommandType = CommandType.Text
   //'query the neccessary table
   cmd.CommandText = "select * from YourTable"
   DA.SelectCommand = cmd
   
   connex.Open()
   //'fill the datatable
   DA.Fill(dt)
   connex.Close()

   XML File

5. We then use the Stringwriter property in combination with the DataTable XMLwriter to produce the text for the RichTextBox.

   The first part of the information produced is the schema as seen in my attachment.

   Below is how the main elements should look like:

   <mcsc>
       <El1>DuckD33</ El1>
       < El2>Marcus</ El2>
       < El3>Cole</ El3>
       < El4>MOD</ El4>
       < El5>2015-02-05T16:49:41.24-06:00</ El5>
       < El6>colem2</ El6>
     </mcsc>

6. The DataTable XMLWriter method is then called again to make the XML file for use as you please. In this case, we are to make an XDocument and run a Linq query.
7. The Xdocument(xdoc) is made by calling the XDocument.Load method which loads the XML file into this format.
8. We then use a couple of the XDocuments methods to produce 2 pieces of text for the RichTextBox.
   • DocumentElement
   • True

   For further method to produce text or alike, please see the link below:

   https://msdn.microsoft.com/en-us/library/system.xml.linq.xdocument_properties(v=vs.110).aspx

   Dim writer As New System.IO.StringWriter
   dt.WriteXml(writer, XmlWriteMode.WriteSchema, False) 
   RichTextBox1.AppendText(writer.ToString)
   //'Wrote the xml file to a file in the Pograms Bin Folder
   dt.WriteXml("..\mcsc.xml")
   Dim xdoc As XDocument = XDocument.Load("..\mcsc.xml")
   RichTextBox1.AppendText(vbCrLf)
   RichTextBox1.AppendText(xdoc.Root.Name.ToString())
   RichTextBox1.AppendText(vbCrLf)
   RichTextBox1.AppendText(xdoc.Root.HasElements.ToString())
   RichTextBox1.AppendText(vbCrLf)

9. Then the Xdocument is used with Linq to query what is in the XDocument. The most important part of the Linq query is to place the correct Column Name in the string Part of the XDocument.Descendents method.

   The Linq query yields a count result and some values associated to the column for which I called them from.

   • Count
   • Val1
   • Val2
   • etc.

   That is the basics of making an XML file from an SQL query in Visual Basic.

   RichTextBox1.AppendText(query.Count.ToString())
   RichTextBox1.AppendText(vbCrLf)
   For Each item In query
   RichTextBox1.AppendText(item)
   RichTextBox1.AppendText(vbCrLf)
   Next
   End Sub

Points of Interest

My first tip, and hopefully not my last. I just saw a process running on a piece of business software and I wanted to replicate the basics of it.

History

• 6^th May, 2015: Initial version