Inserting new value into multiple node instances

Question

I am trying to update a SSRS report XML so that every single report parameter has the <Hidden>true</Hidden> value set on it. Some parameters will already have the value and can be skipped. In theory if that value is set to false in the designer, instead of <Hidden>false</Hidden>, it just doesn't include the node all together, but I'd prefer to look for and replace any instances of that just to be sure.

The problem I am having is that a given report can have X amount of Report Parameters on it. All attempts I have done to use XML.modify only works on the first parameter. Is there some way I can do this update to all instances of ReportParameter?

This is an example of some truncated rdl code that just has the parameters on it:

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<ReportParameters>
    <ReportParameter Name="FederalEmployerIdentificationKey">
      <DataType>Integer</DataType>
      <Prompt>Federal Employer Identification</Prompt>
      <MultiValue>true</MultiValue>
    </ReportParameter>
   <ReportParameter Name="ThirdPartySickPayAddedToOASDIWages">
      <DataType>Float</DataType>
      <Nullable>true</Nullable>
      <Prompt>3rd Party Sick Pay Added To Social Security Wages</Prompt>
      <Hidden>false</Hidden>
    </ReportParameter>
    <ReportParameter Name="ShortDateFormat">
      <DataType>String</DataType>
      <Hidden>true</Hidden>
    </ReportParameter>
</ReportParameters>
</Report>

The first parameter has no <Hidden> node at all, the second has <Hidden>false</Hidden>, and the third <Hidden>true</Hidden>. So the first would need an insert, the second an update (or delete and then insert), and the third doesn't need anything done to it.

These are the updates I have tried so far. They do replace/insert as told, but only on the first instance of a <ReportParameter>.

    --Replace Hidden false with Hidden true, if it is found
    UPDATE @xmlTable
    SET ContentXml.modify('
        replace value of 
        (/*:Report/*:ReportParameters/*:ReportParameter[*:Hidden="false"]
        /*:Hidden/text())[1] 
        with "true"
    ')
    WHERE ContentXml.exist('/*:Report/*:ReportParameters/*:ReportParameter[*:Hidden="false"]')=1;

    --Insert Hidden true if no Hidden is found
    UPDATE @xmlTable
    SET ContentXml.modify('
        insert 
        (<Hidden>true</Hidden>)
        as last 
        into (//*:ReportParameters/*:ReportParameter)[1]
    ')
    WHERE ContentXml.exist('(/*:Report/*:ReportParameters/*:ReportParameter/*:Hidden)')=0;

From what I can see in researching this, these modify functions only work on one node at a time... but I have to believe there are ways to do this in these circumstances... I think I might just not know the correct terminology to Google with.

Answer 1

Please try the following approach.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, ContentXml XML);
INSERT @tbl (ContentXml) VALUES
(N'<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<ReportParameters>
    <ReportParameter Name="FederalEmployerIdentificationKey">
      <DataType>Integer</DataType>
      <Prompt>Federal Employer Identification</Prompt>
      <MultiValue>true</MultiValue>
    </ReportParameter>
   <ReportParameter Name="ThirdPartySickPayAddedToOASDIWages">
      <DataType>Float</DataType>
      <Nullable>true</Nullable>
      <Prompt>3rd Party Sick Pay Added To Social Security Wages</Prompt>
      <Hidden>false</Hidden>
    </ReportParameter>
    <ReportParameter Name="ShortDateFormat">
      <DataType>String</DataType>
      <Hidden>true</Hidden>
    </ReportParameter>
</ReportParameters>
</Report>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

DECLARE @UPDATE_STATUS BIT = 1;
    
WHILE @UPDATE_STATUS > 0 
BEGIN
    WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
        , 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd)
    UPDATE t
    SET ContentXml.modify('replace value of 
        (/Report/ReportParameters/ReportParameter[Hidden="false"]/Hidden/text())[1] 
        with "true"')
    FROM @tbl AS t
    WHERE ContentXml.exist('/Report/ReportParameters/ReportParameter[Hidden="false"]')=1;
        
    SET @UPDATE_STATUS = @@ROWCOUNT;
END;


SET @UPDATE_STATUS = 1;
    
WHILE @UPDATE_STATUS > 0
BEGIN
    WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
        , 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd)
    UPDATE @tbl
    SET ContentXml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
        insert 
        <Hidden>true</Hidden>
        as last 
        into (/Report/ReportParameters/ReportParameter[not(Hidden)])[1]
    ')
    --WHERE ContentXml.exist('(/Report/ReportParameters/ReportParameter/Hidden)')=0;
    WHERE ContentXml.exist('(/Report/ReportParameters/ReportParameter[not(Hidden)])')=1;
        
    SET @UPDATE_STATUS = @@ROWCOUNT;
END;

 -- after
 SELECT * FROM @tbl;

Output XML

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <ReportParameters>
    <ReportParameter Name="FederalEmployerIdentificationKey">
      <DataType>Integer</DataType>
      <Prompt>Federal Employer Identification</Prompt>
      <MultiValue>true</MultiValue>
      <Hidden>true</Hidden>
    </ReportParameter>
    <ReportParameter Name="ThirdPartySickPayAddedToOASDIWages">
      <DataType>Float</DataType>
      <Nullable>true</Nullable>
      <Prompt>3rd Party Sick Pay Added To Social Security Wages</Prompt>
      <Hidden>true</Hidden>
    </ReportParameter>
    <ReportParameter Name="ShortDateFormat">
      <DataType>String</DataType>
      <Hidden>true</Hidden>
    </ReportParameter>
  </ReportParameters>
</Report>

Answer 2

Here is a powershell script that will work

using assembly System.Xml.Linq

$inputFilename = "c:\temp\test.xml"
$outputFilename = "c:\temp\test1.xml"


$doc = [System.Xml.Linq.XDocument]::Load($inputFilename)
$ns = $doc.Root.GetDefaultNamespace()

$parameters = $doc.Descendants($ns + "ReportParameter")

foreach($parameter in $parameters)
{
 
   $hidden = $parameter.Element($ns + "Hidden")
   if($hidden -eq $null)
   {
      $parameter.Add([System.Xml.Linq.XElement]::new($ns + [System.Xml.Linq.XName]::get('Hidden'),'true'))
   }
   else
   {
      $hidden.SetValue('true')
   }
}
$doc.Save($outputFilename)