Read from xml file stored in a SQL database table’s field.

[dphill]

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="CRCPermis sion">
<ToolBars>
<Permission>
<PermissionName >Security Tool Bar</PermissionName>
<GrantAccess>Tr ue</GrantAccess>
</Permission>
<Permission>
<PermissionName >Employee Tool Bar</PermissionName>
<GrantAccess>Tr ue</GrantAccess>
</Permission>
<Permission>
<PermissionName >Case Find Tool Bar</PermissionName>
<GrantAccess>Tr ue</GrantAccess>
</Permission>
<Permission>
<PermissionName >Case Look Up Tool Bar</PermissionName>
<GrantAccess>Tr ue</GrantAccess>
</Permission>
<Permission>
<PermissionName >Allegations Tool Bar</PermissionName>
<GrantAccess>Fa lse</GrantAccess>
</Permission>
<Permission>
<PermissionName >Subjects Tool Bar</PermissionName>
<GrantAccess>Tr ue</GrantAccess>
</Permission>
<Permission>
<PermissionName >Hiring Authority Tool Bar</PermissionName>
<GrantAccess>Fa lse</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.Collecti ons.Generic
Imports System.Componen tModel
Imports System.Data
Imports System.Drawing
Imports System.Text
Imports System.Windows. Forms
Imports System.Data.Sql Client
Imports System.Xml
Imports System.Xml.XPat h
Imports System.Data.Sql Types
Imports System.Configur ation
Imports Microsoft.Data. SqlXml
Imports System.Data.Ole Db

Public Class Form1
Dim WithEvents xmldoc2 As Xml.XmlDataDocu ment ' "MAKE GLOBAL"
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click

Dim investigationsi nfo 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
investigationsi nfo.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.D ocumentElement
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.SelectNode s("descendant:: ToolBars/Permission")
elemList1 = InvestDataSet.G etElementsByTag Name("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.ChildN odes
If NewItem Then
lv = ListView1.Items .Add(Child.Inne rText)
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.0 0
Microsoft Data Access Components (MDAC) 2000.085.1117.0 0
(xpsp_sp2_rtm.0 40803-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.SelectNode s("descendant:: Document")
elemList1 = InvestDataSet.G etElementsByTag Name("Document" )

Help Please

--
thanks for your help...