Reading an RDLC XML file in VB.NET

John Rugo
John Rugo used Ask the Experts™
on
Hi all,
I'm having a problem reading in an RDLC xml file.  My purpose is to collect information about the dataset(s), e.g. Dataset Name and Table name.
I've been dealing with xml projects for decades and for some reason every piece of code I try returns nothing.
Try
            Dim xmlDOC As New XmlDocument()
            xmlDOC.Load(XMLFILE)

            Dim nodeList As XmlNodeList = xmlDOC.SelectNodes("/Report/DataSets/DataSet")
            MsgBox(nodeList.Count)
            For Each node As XmlNode In nodeList
                MsgBox("DataSet Name: " & node("Name").InnerText)
            Next



        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
Are you using WinForms or ASP.Net or WPF?
John RugoSenior SQL DBA

Author

Commented:
WinForms
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
The issue is that the RDLC xml uses XML namespaces:
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">
</Report>

Open in new window

You'll have to define (and use) a namespace manager, and you'll have to add a prefix to the xpath default elements (that don't have an explicit one in the document).
See the paragraph "Namespaces in XPath Expressions" here:
Select Nodes Using XPath Navigation
https://docs.microsoft.com/en-us/dotnet/standard/data/xml/select-nodes-using-xpath-navigation?view=netframework-4.7.2

Enough talk, here's a piece of PowerShell code that returns something.
$xmlDoc = [System.Xml.XmlDocument]::New()
$xmlDoc.Load("C:\Temp\Report.rdlc")

$nsMgr = [System.Xml.XmlNamespaceManager]::New($xmlDoc.NameTable)
$nsMgr.AddNamespace("ns", $xmlDoc.Report.GetAttribute("xmlns"))
$nsMgr.AddNamespace("rd", $xmlDoc.Report.GetAttribute("xmlns:rd"))

$nodeList = $xmlDoc.SelectNodes("ns:Report/ns:DataSets/ns:DataSet", $nsMgr)
ForEach ($node In $nodeList) {
	"DataSet Name: " + $node.GetAttribute("Name")
}

Open in new window

Note: to access, for example, the "DataSourceID" node (note the explicit "rd" namespace), the XPath would look like this:
$xmlDoc.SelectNodes("ns:Report/ns:DataSources/ns:DataSource/rd:DataSourceID", $nsMgr)

Open in new window


For anyone interested: sample files are here:
Sample Data and Report for Print Walkthrough
https://msdn.microsoft.com/en-us/library/ms251734.aspx
John RugoSenior SQL DBA

Author

Commented:
I had a feeling it was something unique to the RDLC xml format.  
I tried the below code; but still not returning anything.

Dim xmlDOC As New XmlDocument()
            xmlDOC.Load(XMLFILE)

            Dim nsMgr As XmlNamespaceManager = New XmlNamespaceManager(xmlDOC.NameTable)
            nsMgr.AddNamespace("ns", "xmlns")
            nsMgr.AddNamespace("rd", "xmlns:rd")

            Dim nodeList As XmlNodeList = xmlDOC.SelectNodes("ns:Report/ns:DataSets/ns:DataSet", nsMgr)
            MsgBox(nodeList.Count)
            For Each node As XmlNode In nodeList
                MsgBox("DataSet Name: " & node("Name").InnerText)
            Next

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Have you tried the PowerShell script with the file you're working on?
John RugoSenior SQL DBA

Author

Commented:
I got the following error in PowerShell....

PS C:\WINDOWS\system32> xmlDoc = [System.Xml.XmlDocument]::New()
At line:1 char:40
+ xmlDoc = [System.Xml.XmlDocument]::New()
+                                        ~
An expression was expected after '('.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : ExpectedExpression
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Copy and paste error: you missed the very first "$" in front of "xmlDoc". Try the "Select all" link under the code block.
John RugoSenior SQL DBA

Author

Commented:
I pasted the command into PowerShell and hit return.  just a new line to type in.  I'm not very familiar with PowerShell with the acception of Script-Lets.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Define "just a new line to type in".
A new line starting with ">>", or a new line starting with "PS C:\>"?
If the former, you missed the closing "}" in the very last line.
If the latter, your rdlc file has a different format, and you'll have to post the relevant parts (that is, all nodes forming the XPath to the DataSet nodes you want to retrieve.
Copied from here and pasted back into a console, tested with the rdlc file I linked above.
PS C:\> $xmlDoc = [System.Xml.XmlDocument]::New()
PS C:\> $xmlDoc.Load("C:\Temp\Report.rdlc")
PS C:\>
PS C:\> $nsMgr = [System.Xml.XmlNamespaceManager]::New($xmlDoc.NameTable)
PS C:\> $nsMgr.AddNamespace("ns", $xmlDoc.Report.GetAttribute("xmlns"))
PS C:\> $nsMgr.AddNamespace("rd", $xmlDoc.Report.GetAttribute("xmlns:rd"))
PS C:\>
PS C:\> $nodeList = $xmlDoc.SelectNodes("ns:Report/ns:DataSets/ns:DataSet", $nsMgr)
PS C:\> ForEach ($node In $nodeList) {
>> "DataSet Name: " + $node.GetAttribute("Name")
>> }
DataSet Name: Sales
PS C:\>

Open in new window

John RugoSenior SQL DBA

Author

Commented:
Thank you.  That worked and it did return the one dataset name in the report, which happens to be "DataSet1".
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
On closer inspection, your lines 5 and 6 don't work.
The strings "xmlns" and "xmlns:rd" are not the namespaces used, they're the attributes that hold the namespaces.
You need to use the GetATtribute() method like I did in lines 5 and 6.
Your script won't retrieve the dataset name, either, because the name is not in the node's InnerText, but in the node's attribute "Name". Like for the namespaces, you'll need the node's GetAttribute() method.
John RugoSenior SQL DBA

Author

Commented:
Doesn't recognize the .Report property of xmlDoc.

 Dim nsMgr As XmlNamespaceManager = New XmlNamespaceManager(xmlDOC.NameTable)
            nsMgr.AddNamespace("ns", xmlDOC.report.GetAttribute("xmlns"))
            nsMgr.AddNamespace("rd", xmlDOC.Report.GetAttribute("xmlns:rd"))
John RugoSenior SQL DBA

Author

Commented:
Report is not a member of xmlDocument.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Just try
             nsMgr.AddNamespace("ns", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition")
             nsMgr.AddNamespace("rd", "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner")

Open in new window

John RugoSenior SQL DBA

Author

Commented:
I was just thinking about that too :)
unfortunately I'm getting back a nodeList.count of 0. Should I not get something back?

Dim xmlDOC As New XmlDocument()
            xmlDOC.Load(XMLFILE)

            Dim nsMgr As XmlNamespaceManager = New XmlNamespaceManager(xmlDOC.NameTable)
            nsMgr.AddNamespace("ns", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition")
            nsMgr.AddNamespace("rd", "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner")

            Dim nodeList As XmlNodeList = xmlDOC.SelectNodes("ns:Report/ns:DataSets/rd:DataSet", nsMgr)
            MsgBox(nodeList.Count)
            For Each node As XmlNode In nodeList
                MsgBox("DataSet Name: " & node("Name").InnerText)
            Next

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Because you now used the "rd" namespace for the DataSet; just look at the XML, the DataSet node uses the default namespace, here as "ns".
The "rd" namespace I mentioned above was only valid for the "DataSourceID" node.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
And you still need to replace the part in which you get the DataSet's name.
Again: the DataSet's name is in the attribute "Name".
Will probably amount to something like
    MsgBox("DataSet Name: " & node.GetAttribute("Name")

Open in new window

John RugoSenior SQL DBA

Author

Commented:
There is no GetAttribute property for node.
I get the following value when running the below code.....

DataSet Name: System.Xml.XmlAttribute


Dim xmlDOC As New XmlDocument()
            xmlDOC.Load(XMLFILE)

            Dim nsMgr As XmlNamespaceManager = New XmlNamespaceManager(xmlDOC.NameTable)
            nsMgr.AddNamespace("ns", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition")
            nsMgr.AddNamespace("rd", "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner")

            Dim nodeList As XmlNodeList = xmlDOC.SelectNodes("ns:Report/ns:DataSets/ns:DataSet", nsMgr)

        For Each node As XmlNode In nodeList
            MsgBox("DataSet Name: " & node.Attributes.GetNamedItem("Name").ToString)
        Next

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
At the end of line 11, that would need to be .ToString(); it's a method, not a property.
John RugoSenior SQL DBA

Author

Commented:
got it.

node.Attributes("Name").Value
John RugoSenior SQL DBA

Author

Commented:
Trying to get the Table Name(s) now.  Returning nothing.

 Dim xmlDOC As New XmlDocument()
            xmlDOC.Load(XMLFILE)

            Dim nsMgr As XmlNamespaceManager = New XmlNamespaceManager(xmlDOC.NameTable)
            nsMgr.AddNamespace("ns", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition")
            nsMgr.AddNamespace("rd", "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner")

            Dim nodeList As XmlNodeList = xmlDOC.SelectNodes("ns:Report/ns:DataSets/ns:DataSet", nsMgr)

            For Each node As XmlNode In nodeList
                MsgBox("DataSet Name: " & node.Attributes("Name").Value)

                Dim nodeList_Tables As XmlNodeList = xmlDOC.SelectNodes("ns:Report/ns:DataSets/ns:DataSet/rd:DataSetInfo/rd:TableName", nsMgr)
                For Each node_tbl As XmlNode In nodeList_Tables
                    MsgBox("Table Name: " & node_tbl.Value)
                Next
            Next

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Can't tell without the XML. Might be the path and/or namespace, or that you're accessing the result with .Value
John RugoSenior SQL DBA

Author

Commented:
I took out the report design elements and columns to condense it down to be much more readable.
the table name is "SelectScript".
By the way, thank you so much for help me and you patience.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <DataSources>
    <DataSource Name="DataSet1">
      <ConnectionProperties>
        <DataProvider>System.Data.DataSet</DataProvider>
        <ConnectString>/* Local Connection */</ConnectString>
      </ConnectionProperties>
      <rd:DataSourceID>87380aff-faaf-405d-9335-fada0e622245</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSet1</DataSourceName>
        <CommandText>/* Local Query */</CommandText>
      </Query>
      <Fields>
      </Fields>
      <rd:DataSetInfo>
        <rd:DataSetName>DataSet1</rd:DataSetName>
        <rd:SchemaPath></rd:SchemaPath>
        <rd:TableName>ScriptSelect</rd:TableName>
        <rd:TableAdapterFillMethod>Fill</rd:TableAdapterFillMethod>
        <rd:TableAdapterGetDataMethod />
        <rd:TableAdapterName>ScriptSelectTableAdapter</rd:TableAdapterName>
      </rd:DataSetInfo>
    </DataSet>
  </DataSets>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>569892cc-4847-403b-a49d-403de0420c11</rd:ReportID>
</Report>

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
Your XPath is correct, so it must
PowerShell (with the preparations from above) would be
$xmlDoc.SelectNodes("ns:Report/ns:DataSets/ns:DataSet/rd:DataSetInfo/rd:TableName", $nsMgr) | ForEach-Object {"TableName: $($_.InnerText)"}

Open in new window

This time, the name is actually in the InnerText property, so try something like this:
MsgBox("Table Name: " & node_tbl.InnerText)

Open in new window

John RugoSenior SQL DBA

Author

Commented:
Up that worked.  Thank you so much for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial