I have a simple xml document that I want to create a excel spreadsheet from. My vb code is only working when the xml document does not have report parent with child elements.
How do I change the For Each statement so the code does not fail on second loop where is does not find the following elements FName, MName, LName, LogIn and Organization
For example it is working for this xml
I am using objStreamWriter to output excel sheet.
xml file name is saved in objXmlResultDoc
Desired output in 2 rows with 8 column
FirstName MiddleName LastName LogIn Organization OrganizationORI OrganizationName CaseNumber
Someone X Pati File\JeanX 999 NULL NULL 2312
Null Null Null Null Null 999 Googleee
2312
My code does not work for the following xml document
<?xml version="1.0" encoding="UTF-8"?>
<Results>
<Report>
<FName>Someone</FName>
<MName>X</MName>
<LName>Pati</LName>
<LogIn>File\JeanX</LogIn>
<Organization>999</Organization>
<CaseNumber>2312</CaseNumber>
</Report>
<Report>
<OrganizationORI>999</OrganizationORI>
<OrganizationName>Googleee</OrganizationName>
<CaseNumber>2312</CaseNumber>
</Report>
</Results>
Vb code
Dim objXMLNode As XmlNode
Dim objXmlResultDoc As XmlDocument
Dim strFName As String
Dim strMName As String
Dim strOrganizationName As String
Dim strLName As String
Dim strLogIn As String
Dim strOrganization As String
Dim strCaseNumber As String
objXMLNode = objXmlResultDoc.DocumentElement.SelectSingleNode("Report")
objOutputMemStream = New System.IO.MemoryStream
objStreamWriter = New System.IO.StreamWriter(objOutputMemStream)
objStreamWriter.WriteLine("Requestor First Name, Requestor Middle Name, Requestor Last Name, Requestor Login, Organization ID, Organization Name, Case Number")
objXMLNode = objXmlResultDoc.DocumentElement.SelectSingleNode("Report")
For Each objXMLNode In objXmlResultDoc.DocumentElement.SelectNodes("Report")
If objXMLNode.SelectSingleNode("Report/"FName") Is Nothing Then
strFirstName = objXMLNode.SelectSingleNode(FName").InnerText
Else
strFirstName = "NULL"
End If
If objXMLNode.SelectSingleNode("Report/"MiddleName") Is Nothing Then
strMiddleName = objXMLNode.SelectSingleNode("MiddleName").InnerText
Else
strMiddleName = "NULL"
End If
'Get LastName
If objXMLNode.SelectSingleNode("Report/"LastName") Is Nothing Then
strLastName = objXMLNode.SelectSingleNode("LastName").InnerText
Else
strLastName = "NULL"
End If
If objXMLNode.SelectSingleNode("Report/"LogIn") Is Nothing Then
strLogIn = objXMLNode.SelectSingleNode("LogIn").InnerText
Else
strLogIn = "NULL"
End If
'Get Organization
If objXMLNode.SelectSingleNode("Report/"Organization") Is Nothing Then
strOrganization = objXMLNode.SelectSingleNode("Organization").InnerText
Else
strOrganization = "NULL"
End If
If objXMLNode.SelectSingleNode("Report/"CaseNumber") Is Nothing Then
strCaseNumber = objXMLNode.SelectSingleNode(CaseNumber").InnerText
Else
strCaseNumber = "NULL"
End If
objStreamWriter.WriteLine(strFName + "," + strMName + "," + strLName + "," + strLogIn + "," + strOrganization + "," + strOrganizationName + "," + strCaseNumber)
Next
objStreamWriter.Flush()
objOutputMemStream.Position = 1
What I have tried:
Dim strLName As String
Dim strLogIn As String
Dim strOrganization As String
Dim strCaseNumber As String
objXMLNode = objXmlResultDoc.DocumentElement.SelectSingleNode("Report")
objOutputMemStream = New System.IO.MemoryStream
objStreamWriter = New System.IO.StreamWriter(objOutputMemStream)
objStreamWriter.WriteLine("Requestor First Name, Requestor Middle Name, Requestor Last Name, Requestor Login, Organization ID, Organization Name, Case Number")
objXMLNode = objXmlResultDoc.DocumentElement.SelectSingleNode("Report")
For Each objXMLNode In objXmlResultDoc.DocumentElement.SelectNodes("Report")
If objXMLNode.SelectSingleNode("Report/"FName") Is Nothing Then
strFirstName = objXMLNode.SelectSingleNode(FName").InnerText
Else
strFirstName = "NULL"
End If
If objXMLNode.SelectSingleNode("Report/"MiddleName") Is Nothing Then
strMiddleName = objXMLNode.SelectSingleNode("MiddleName").InnerText
Else
strMiddleName = "NULL"
End If
'Get LastName
If objXMLNode.SelectSingleNode("Report/"LastName") Is Nothing Then
strLastName = objXMLNode.SelectSingleNode("LastName").InnerText
Else
strLastName = "NULL"
End If
If objXMLNode.SelectSingleNode("Report/"LogIn") Is Nothing Then
strLogIn = objXMLNode.SelectSingleNode("LogIn").InnerText
Else
strLogIn = "NULL"
End If
'Get Organization
If objXMLNode.SelectSingleNode("Report/"Organization") Is Nothing Then
strOrganization = objXMLNode.SelectSingleNode("Organization").InnerText
Else
strOrganization = "NULL"
End If
If objXMLNode.SelectSingleNode("Report/"CaseNumber") Is Nothing Then
strCaseNumber = objXMLNode.SelectSingleNode(CaseNumber").InnerText
Else
strCaseNumber = "NULL"
End If
objStreamWriter.WriteLine(strFName + "," + strMName + "," + strLName + "," + strLogIn + "," + strOrganization + "," + strOrganizationName + "," + strCaseNumber)
Next
objStreamWriter.Flush()
objOutputMemStream.Position = 1