Reading an RDLC XML file in VB.NET

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

LVL 4
Thunder724Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chinmay PatelChief Technical NinjaCommented:
Are you using WinForms or ASP.Net or WPF?
Thunder724Author Commented:
WinForms
oBdACommented:
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Thunder724Author 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

oBdACommented:
Have you tried the PowerShell script with the file you're working on?
Thunder724Author 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
oBdACommented:
Copy and paste error: you missed the very first "$" in front of "xmlDoc". Try the "Select all" link under the code block.
Thunder724Author 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.
oBdACommented:
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

Thunder724Author Commented:
Thank you.  That worked and it did return the one dataset name in the report, which happens to be "DataSet1".
oBdACommented:
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.
Thunder724Author 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"))
Thunder724Author Commented:
Report is not a member of xmlDocument.
oBdACommented:
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

Thunder724Author 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

oBdACommented:
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.
oBdACommented:
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

Thunder724Author 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

oBdACommented:
At the end of line 11, that would need to be .ToString(); it's a method, not a property.
Thunder724Author Commented:
got it.

node.Attributes("Name").Value
Thunder724Author 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

oBdACommented:
Can't tell without the XML. Might be the path and/or namespace, or that you're accessing the result with .Value
Thunder724Author 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

oBdACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thunder724Author Commented:
Up that worked.  Thank you so much for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.