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
John RugoSenior SQL DBAAsked:
Who is Participating?
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 Technology NinjaCommented:
Are you using WinForms or ASP.Net or WPF?
John RugoSenior SQL DBAAuthor 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
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

John RugoSenior SQL DBAAuthor 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?
John RugoSenior SQL DBAAuthor 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.
John RugoSenior SQL DBAAuthor 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

John RugoSenior SQL DBAAuthor 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.
John RugoSenior SQL DBAAuthor 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 DBAAuthor 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

John RugoSenior SQL DBAAuthor 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

John RugoSenior SQL DBAAuthor 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.
John RugoSenior SQL DBAAuthor Commented:
got it.

node.Attributes("Name").Value
John RugoSenior SQL DBAAuthor 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
John RugoSenior SQL DBAAuthor 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
John RugoSenior SQL DBAAuthor 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.