Use SQLXML with VB.NET

Question

I'm new in .NET programming and Visual Basic and I have no much idea about it. I have the next code:

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim cnn As New SqlConnection("Server=adserver;uid=sa;pwd=1234;dat abase=empatic")
        Dim da As New SqlDataAdapter("select * from producte for xml path('producte'),     root('Productes')", cnn)
        Dim ds As New DataSet        
        da.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)
        Dim a As String = ds.Tables(0).ToString

    End Sub
End Class

What this code does is connect to a SQL Server and execute a query. This query is generated in a single XML line in a GridViewer. My question is: How I can do to get the information when in VB, show me the xml-shaped rather than a single line.

The ultimate purpose of all this is that the VB to connect to SQL Server and clicking a button the program show me the xml I generated with the query.

What type of application is this (Winforms, Web, etc)? Also, I do hope that your connection string does not contain the actual sa password. If so, you should change it immediately (both in the question and on your server, given what the value is). — Esoteric Screen Name, Jan 18 '13 at 20:16
Hi, thanks for your answer @EsotericScreenName . First, the password is not the actual for my server. The project is Winforms. — polgrana, Jan 18 '13 at 20:22

score 1 · Answer 1 · answered Jan 18 '13 at 21:34

To view your XML in a navigable tree format, you should use the TreeView control. In order to set the data source, you'll also need to make your XML in to a collection of TreeNodes. Here's how I do this, by making an XmlDocument and then using a conversion function to make the TreeNodes:

Use SqlDataReader to select the output from your query in to a string variable, and then you can populate the TreeView like this:

Try
  Dim xsd As XmlDocument = New XmlDocument()
  xsd.LoadXml(myXmlString)
  ConvertXmlNodeToTreeNode(xsd.FirstChild, myTreeView.Nodes)
  myTreeView.Nodes(0).ExpandAll() 'expand the root
Catch ex As Exception
  Throw New Exception("Failed to create XmlDocument", ex)
End Try

Conversion function:

Private Sub ConvertXmlNodeToTreeNode(ByVal xmlNode As XmlNode, ByVal treeNodes As TreeNodeCollection)
  Dim newTreeNode As TreeNode = treeNodes.Add(xmlNode.Name)

  Select Case xmlNode.NodeType
    Case XmlNodeType.ProcessingInstruction, XmlNodeType.XmlDeclaration
      newTreeNode.Text = "<?" + xmlNode.Name + " " +
      xmlNode.Value + "?>"
    Case XmlNodeType.Element
      newTreeNode.Text = "<" + xmlNode.Name + ">"
    Case XmlNodeType.Attribute
      newTreeNode.Text = "ATTRIBUTE: " + xmlNode.Name
    Case XmlNodeType.Text, XmlNodeType.CDATA
      newTreeNode.Text = xmlNode.Value
    Case XmlNodeType.Comment
      newTreeNode.Text = "<!--" + xmlNode.Value + "-->"
  End Select

  If Not IsNothing(xmlNode.Attributes) Then
    For Each attrib As XmlAttribute In xmlNode.Attributes
      ConvertXmlNodeToTreeNode(attrib, newTreeNode.Nodes)
    Next
  End If
  For Each childNode As XmlNode In xmlNode.ChildNodes
    ConvertXmlNodeToTreeNode(childNode, newTreeNode.Nodes)
  Next
End Sub

Thank you a lot!!! I have changed some things that you recommended and it works properly! Now, I have to export the result of the view to a XML document. Can you help me? — polgrana, Jan 21 '13 at 16:21

score 0 · Accepted Answer · answered Jan 18 '13 at 21:22

0

Do you want the data as XML? If not, just remove the for xml path... from your SQL statement. For instance, you could just do this:

Dim da As New SqlDataAdapter("select * from producte", cnn)

If you do want it as XML, then there are several different options available to you for parsing the XML. Here are the most common options:

answered Jan 18 '13 at 21:22

Steven Doggart

43,358
8
68
105

Thanks @Steven Doggart! I've searched for XDocument and It has worked perfectly! – polgrana Jan 22 '13 at 15:59

Use SQLXML with VB.NET

2 Answers2