Unusual result from empty XML nodes

[303factory]

Good afternoon

I'm writing code to read in some XML files and the nodes which are empty seem to be returning strange results with a non-ascii character

Dim MessageList As IXMLDOMNodeList
Dim MessageNode As IXMLDOMNode
Dim childNode As IXMLDOMNode
       
Set MessageList = objXMLFile.SelectNodes("/reports/report/sms_messages/sms_message")

For Each MessageNode In MessageList
     If MessageNode.HasChildNodes Then
                For Each childNode In MessageNode.ChildNodes
                    If childNode.nodeName = "id" Then
                        intRegNumber = childNode.Text
                    End If
                    If childNode.nodeName = "number" Then
                        strNumber = childNode.Text
                    End If
                    If childNode.nodeName = "name" Then
                        strName = childNode.Text
                    End If
                    If childNode.nodeName = "timestamp" Then
                        strTimeSTamp = childNode.Text
                    End If
                    If childNode.nodeName = "status" Then
                        strStatus = childNode.Text
                    End If
                     If childNode.nodeName = "text" Then
                        strText = childNode.Text
                    End If
                Next
     End If

For the strNumber value it gives the below value when it should be null/empty
strNumber : " " : String [there is the non-ascii square at the front of this string then several 'spaces']

XML file:

<sms_messages>
<sms_message>
<id>
1
</id>
<number>
</number>
<name>
N/A
</name>
<timestamp>
N/A
</timestamp>
<status>
Sent
</status>
<folder>
Sent
</folder>
<type>
Outgoing
</type>
<text>
On sim
</text>
</sms_message>
<sms_messages>

Can anyone shed any light on this? Am I going about reading in the data incorrectly?

Thanks in advance

303

 

[jal]

The other day someone raised a question about XML so I took a stab at it - this was my first try so I can't say I learned much. But your XML file looks similar to the other guy's file, in the sense that it amounts to one simple table. Your table currently only has one row, but lots of columns.

tblsSimMessages

columns:

sms_message...id...number...timestamp...status...folder...type...text

So I'll just copy and paste the sample code I gave the other guy - it loads the data into a recordset and creates a table whose rows show the data. First the XML file (this contains two rows unlike your 1 row).

<Catalog>
<Rec>
<ITEM>1903</ITEM>
<QTY>15</QTY>
<SUB>Widget 1</SUB>
</Rec>
<Rec>
<ITEM>1800</ITEM>
<QTY>10</QTY>
<SUB>Widget 2</SUB>
</Rec>
</Catalog>

and here's the code:

Private Sub Command0_Click()
   Dim doc As New DOMDocument
   Dim row As IXMLDOMNode
   doc.Load "C:\zz.txt"
   On Error Resume Next
   CurrentDb.Execute "DROP TABLE tbl_DOC"
   On Error GoTo 0
   Dim SQL As String
   SQL = "CREATE TABLE tbl_DOC ("
    'Add columns to the table
   For Each row In doc.documentElement.childNodes
        Dim column As IXMLDOMNode
        For Each column In row.childNodes
                SQL = SQL & column.nodeName & " TEXT(255),"
        Next
        SQL = Left(SQL, Len(SQL) - 1) 'strips off last comma
        SQL = SQL & ")"
        Exit For
   Next
   CurrentDb.Execute SQL 'creates the table
   'Add the rows
   Dim rs As DAO.Recordset
   Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_DOC")
   For Each row In doc.documentElement.childNodes
        rs.AddNew
        For Each column In row.childNodes
                rs.Fields(column.nodeName).Value = column.Text
        Next
        rs.Update
   Next
   MsgBox "Completed"
End Sub

 

[jal]

BTW, if my suggestion doesn't work you might want to keep your own code but with this modification (replace carriage returns and empty spaces)

strNumber = Replace(strNumber, vbCR, "")
strNumber = Replace(StrNumber, vbLF, "")
strNumber = Trim(StrNumber)

or maybe something like this:

if not IsNumeric(strNumber) Then strNumber = ""

 

[303factory]

Thanks for your help I liked your approach to going through the nodes and the trimming functions will be very useful