Hello all I am a beginner .Net programmer so please forgive my ignorance.
In brief, I am trying to read from xml file stored in a SQL database table’s
field.
This is what I used to create the table:
CREATE TABLE Document (
DocID int IDENTITY NOT NULL,
Description nvarchar(50) NOT NULL,
DocumentStore xml NOT NULL,
CONSTRAINT PK_Document PRIMARY KEY CLUSTERED
(
DocID ASC
)
)
This is what I used to insert my XML document into a SQL database table’s
field.
INSERT INTO Document (Description, DocumentStore)
VALUES('Bruce''s poem',
N'<?xml version="1.0" ?>
<Document Name="Poem">
<Author>Bruce</Author>
<Text>The cat/is flat.</Text>
</Document>')
This is my XML document’s contents.
<?xml version="1.0"?>
<Document Name="CRCPermission">
<ToolBars>
<Permission>
<PermissionName>Security Tool Bar</PermissionName>
<GrantAccess>True</GrantAccess>
</Permission>
<Permission>
<PermissionName>Employee Tool Bar</PermissionName>
<GrantAccess>True</GrantAccess>
</Permission>
<Permission>
<PermissionName>Case Find Tool Bar</PermissionName>
<GrantAccess>True</GrantAccess>
</Permission>
<Permission>
<PermissionName>Case Look Up Tool Bar</PermissionName>
<GrantAccess>True</GrantAccess>
</Permission>
<Permission>
<PermissionName>Allegations Tool Bar</PermissionName>
<GrantAccess>False</GrantAccess>
</Permission>
<Permission>
<PermissionName>Subjects Tool Bar</PermissionName>
<GrantAccess>True</GrantAccess>
</Permission>
<Permission>
<PermissionName>Hiring Authority Tool Bar</PermissionName>
<GrantAccess>False</GrantAccess>
</Permission>
</ToolBars>
</Document>
This is my code I use to display my XML document into my form’s list view
control.
Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data.SqlClient
Imports System.Xml
Imports System.Xml.XPath
Imports System.Data.SqlTypes
Imports System.Configuration
Imports Microsoft.Data.SqlXml
Imports System.Data.OleDb
Public Class Form1
Dim WithEvents xmldoc2 As Xml.XmlDataDocument ' "MAKE GLOBAL"
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim investigationsinfo As New SqlDataAdapter("select TOP 1
DocumentStore from Document FOR XML AUTO, TYPE", SqlConnection1)
Dim ds As DataSet
SqlConnection1.Open()
ds = New DataSet()
'filling the dataset
investigationsinfo.Fill(ds, "Document")
Dim InvestDataSet As XmlDataDocument
InvestDataSet = New XmlDataDocument(ds)
'I put this one in to see if it had a different result
Dim root As XmlNode = InvestDataSet.DocumentElement
Dim elemList1 As XmlNodeList
Dim elemList2 As XmlNodeList
Dim Employee As XmlNode
Dim Child As XmlNode
Dim NewItem As Boolean
Dim ict As Integer
Dim Lv
'I want either one to work
elemList2 = root.SelectNodes("descendant::ToolBars/Permission")
elemList1 = InvestDataSet.GetElementsByTagName("ToolBars/Permission")
For Each Employee In elemList1
' Use the Text property of each node
' to write the listview
NewItem = True
ict = 0
For Each Child In Employee.ChildNodes
If NewItem Then
lv = ListView1.Items.Add(Child.InnerText)
NewItem = False
Else
ict = ict + 1
lv.SubItems(ict) = _
Child.InnerText
End If
Next
Next
SqlConnection1.Close()
End Sub
End Class
I am using Microsoft Visual Basic 2005 and SQL Server 2005:
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
This code above only returns some value to the variable when I reference
“Document”.
elemList2 = root.SelectNodes("descendant::Document")
elemList1 = InvestDataSet.GetElementsByTagName("Document")
Help Please
--
thanks for your help...
