Parsing an XML Node in a SQL Server Reporting services report.

Nothing more fun than having to parse XML in a report. 

It is basically the following steps:

- modify the query in the dataset to include the XML column

- In the Report Properties (from the report menu pad)

   * Add a Reference to System.XML

   * Paste the code from the attached txt file in Code section (this code is specific to the XML that I was parsing, but the concept could be modified or enhanced as needed based on your requirements)

- Add a column to the report for what you are getting from the XML (notes in my case) and set the textbox Expression to =Code.ParseXML(Fields!{column here no brackets}.Value)

Below is the code to paste into the code section

Function ParseXML (ByVal xmlText as String) AS String
Dim i as Integer
Dim ret as String
Dim xmlDoc As New System.Xml.XmlDocument
Dim nodeList As System.Xml.XmlNodeList
Dim node As System.Xml.XmlNode

Try
xmlDoc.LoadXml(xmlText)
nodeList = xmlDoc.GetElementsByTagName("fd")

' Loop through the nodelist returned by the "fd" query (should be only 1)
For Each node In nodeList
' Loop through all the child nodes of "fd" and format the key-value
  For i = 0 to node.ChildNodes.Count - 1
      ret = ret & node.ChildNodes.Item(i).InnerText & vbCrLf
  Next
Next

'If the DB is Null this sets the value to "N/A" instead of #Error
Catch ex As Exception
  ret = "N/A"
  Return ret
End Try

'Format as date and time if it's an ETA
Try
Dim dt as DateTime
  dt = DateTime.parse(ret)
  ret = "ETA: " & Format(dt, "General Date")
Catch ex As Exception
End Try

Return ret

End Function

While working on this I found a new XML Editor, I have no idea if it works, but I like it, "the interface is plain and convenient"!