Display XML Select Node values in the asp.net label

[slb2009]

Hi All,

I have a SQL table with XML Data Type (FormXML).
Please see the attached picture 1 of the SQL table for your review.


The SQL table displays data fields with values:

Name: John Mark
CodeNum: 88
Data: 3/19/2009 12:00:00 AM
FormXML:

<FormXML>
<DocInfo>
<Check>
<Num>789789</Num>
<Amount>15.00</Amount>
<AmountNeeded>25.00</AmountNeeded>
</Check>
<PaymentNotes>
<Notes>Call John</Notes>
</PaymentNotes>
<DocumentReasons>
<Reason>Recording Fee Is Not Correct (*see Attached fee schedule)</Reason>
</ DocumentReasons>
< DocumentReasons >
<Reason>Inadequate space on document for recording information, it will be necessary to stamp on back of document creating an additional page.</Reason>
</ DocumentReasons>
<RequirementsInfo>
<Requirement>Lacks grantees mailing address</Requirement>
</RequirementsInfo>
<RequirementsInfo>
<Requirement>Document/legal description is not legible for microfilming/scanning</Requirement>
</RequirementsInfo>
<OtherRequirements>
<OtherRequirement>Document must go to trustee to have the Reconveyance prepared, signed and notarized.</OtherRequirement>
</OtherRequirements>
<SignatureName>
<Signature>Mary Olive</Signature>
</ SignatureName>
<DocDate>
<Date>March 19, 2009</Date>
</DocDate>
<CustomerNum>
<CustCode>88</CustCode>
</CustomerNum>
<CustomerName>
<CustName>House& North</CustName>
</CustomerName>
<CustomerAddress>
<CustomerAddressLine>1234 Street Olympus PO BOX 441 Virginia, OH 78977</CustomerAddressLine>
</CustomerAddress>
</DocInfo>
</FormXML>


DocNum1: 5678
DocNum2: 1543555
DocNum3: 87654
ID: 1


I have Asp.Net web form page (see attached picture 2).
In the web form page shows textbox (txtDocNum.text) for searching doc numbers, the button (searchDocNums) “Search” and XML Select Nodes displaying in the label (LblOutput.text) web control.


What I would like to see, please see the attached picture 3.

In my code : CurrentNodes = xd.SelectNodes("//Reason") shows this following information in the label” Fee is not correct. Inadequate space on document for information. It's good, but incomplete.

When I put CurrentNodes = xd.SelectNodes(“//DocInfo/Check/Num/Amount/AmountNeeded/….etc…”)
Displays nothing. What I am doing wrong? Your help is greatly appreciated.

See my VB.NET code below:

Protected Sub SearchDocNums_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SearchCheckNums.Click

Dim DocNum1 As Integer = Convert.ToInt32(TxtDocNum.Text)
Dim DocNum2 As Integer = Convert.ToInt32(TxtDocNum.Text)
Dim DocNum3 As Integer = Convert.ToInt32(TxtDocNum.Text)


Dim SqlConn As SqlConnection
Dim sConnString As String = System.Configuration.ConfigurationManager.AppSettings("SireConnStr2")

Dim mystring As String = "SELECT FormXML FROM DocReject WHERE DocNum1 = ''' & TxtDocNum & '" Or DocNum2 = ''' & TxtDocNum & '" Or DocNum3 = ''' & TxtDocNum & '" "

Dim cmd As SqlCommand = New SqlCommand(mystring)

SqlConn = New SqlConnection(sConnString)
SqlConn.Open()
cmd.Connection = SqlConn

Dim DataReader As SqlDataReader = cmd.ExecuteReader

Do While DataReader.Read

Dim sqlXmlValue As SqlXml = DataReader.GetSqlXml(0)
Dim xmlReader As XmlReader = sqlXmlValue.CreateReader()
If xmlReader.Read() Then
Lbloutput.Text = GetReasons(sqlXmlValue.Value)

Loop
DataReader.Close()
SqlConn.Close()
End Sub

Function GetReasons(ByVal FormXML As String) As String
Dim xd As New XmlDocument
Dim CurrentNodes As XmlNodeList
Dim ReasonsList As String = String.Empty
Dim i As Integer

Try
xd = New XmlDocument
xd.LoadXml(FormXML)
CurrentNodes = xd.SelectNodes("//Reason")

i = 0
While i < CurrentNodes.Count
ReasonsList = ReasonsList & Trim(CurrentNodes(i).InnerText) & "<br/>" & "<br/>"

i = i + 1
End While
Return ReasonsList

Catch ex As Exception
End Try

End Function

Sincerely Thanks, slb2009