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"!
