I have a powershell script that we run to update an entire directories worth of RDL files, by changing their connection string from shared to embedded, and adding Hidden = True tags to each parameter.
This has worked fine until we recently ran into an older report. Part of the connection update is to set the Security type from None to Database. I found in these few instances, it did't update the SecurityType node, but instead added an entirely new instance of it, causing the report to err when opened in Report Builder.
When examining the XML, I noticed that the root node only had the default namespace in it, it did not contain the rd reference. The SecurityTYpe node however is setup with the rd reference...when powershell hits this it can't read it in and it thinks there is no SecurityType node, so it writes a new one. This is the offending root node...
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">
And this is how the SecurityType node looks in that same report:
<rd:SecurityType xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">None</rd:SecurityType>
If I manually add the rd namespace to the Report node, then powershell works fine. So I am wondering how I can update that to instead be:
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
This is how the PS script (using System.Xml.Linq) is grabbing the xml and the namespaces:
$ns = $doc.Root.GetDefaultNamespace()
$rd = $doc.Root.GetNamespaceOfPrefix("rd");
$p1 = $doc.Root.GetNamespaceOfPrefix("p1");
We then loop through the DataSource node to get its different elements:
foreach($dataSource in $dataSources)
{
$dataSourceName = $dataSource.FirstAttribute.Value
$securityType = $dataSource.Element($rd + "SecurityType")
$securityTypeVal = $securityType.Value
...
It is here though that the problem occurs with setting both $securityType and $securityTypeVal...they return NULL even though they do exist.
if($securityType -eq $null)
{
$dataSource.Add([System.Xml.Linq.XElement]::new($rd + [System.Xml.Linq.XName]::get('SecurityType'),'Database'))
}
else
{
if($securityTypeVal -ne "Database")
{
$securityType.SetValue('Database')
$securityTypeVal = $dataSource.Element($rd + "SecurityType").Value
}
}
Is there some way else I should be accounting for the missing rd namespace prefix in the root when trying to handle SecurityType...or is there a way I can look to see if it is there, and add it if its missing? I've been trying all sorts of cobbled up ways to add it but I am far from an expert in this language and everything I have done has resulted in various errors. I appreciate any help on this.