powershell script help...in replacing string from a xml file which I am reading as string

Article
2016-06-22

Question

_{Wednesday, June 22, 2016 9:21 AM}

I want to replace attribute value from a xml file

As xml is bit complicated The specific value of attribute[to be updated attribute is Data Source=DEGSQLDWVA23\ONECLICK_D_01>Replaced by DEGSQLDWVA23\NewConnection ] which I want to change is present under CommandText node. I can reach till that node using below line-

$dataset.ChildNodes.Query.CommandText

but this returns me another xml where my attribute (to be changed) is present but not getting way how to replace it and save changes in actual xml.

If I take it as string and then I do find and replace it works but xml formatting issue comes also it appends <object> and other extra nodes.

$str=$dataset.ChildNodes.Query.CommandText | ConvertTo-Xml -As String

If xml is needed I can provide it.

All replies (11)

_{Wednesday, June 22, 2016 9:50 AM}

Replace or Find?

Regards,

Farshid Mahdavipour, MCSE, PMP

Blog: www.sharepointjunkies.com

Please don't forget to mark it as answered, if your problem resolved or helpful.

_{Wednesday, June 22, 2016 11:28 AM}

Hello Farshid,

Replace and find as a string is done now which is causing formatting issue in existing xml file .

I think I need to take it as xml only not as string then find attribute and replace as attribute only after reaching particular node.

sudhanshu sharma Do good and cast it into river :)

_{Friday, June 24, 2016 9:34 AM}

Hi Sharma,

>>Replace and find done now append and overwrite file is pending

Try this:

If(Test-Path xxx.xml)
{
  Remove-Item xxx.xml
  $str | Out-Clixml xx.xml
}

Best regards,

Andy_Pan

_{Friday, June 24, 2016 10:18 AM}

Hi Andy I have done this only issue is while overwriting my xml format is not coming properly -

(but I am not sure if issue is coming because I am taking xml now as string and replacing. Is it culprit for xml formatting issue. Should I change my approach?)

it appends <objects > , lt, gt and somany extra tags

my file is rdl file (have xml nodes) I am taking whole filw in string and replacing and overwriting but after overwriting xml formatting is not fine

my script

$fileDirectory = "C:\Staging"

write -host "Got Directory"

$fileColl=Get-ChildItem $fileDirectory

$xml = [xml](Get-Content ($fileDirectory +"\+"Employee Participation.rdl"));

$AllNodes=$xml.OuterXml

write -host "got content of rdl as dataset"

#write -host "inside loop"

$actualFile=$AllNodes | ConvertTo-Xml -As String

#write -host "actual string"$actualFile

if($actualFile.Contains("DEGSQLDWVA23\ONECLICK_D_01") )

{

write -host "true"

$changeText=$actualFile -replace "DEGSQLDWVA23\ONECLICK_D_01", "NewValue"

#$changeText =[xml]$changeText

#Format -XML -InputObject $changeText

#write -host "replaced"$changeText

$filepath=$fileDirectory +"\+"Employee Participation.rdl"

out-file -filepath $filepath -inputobject $changeText -encoding UTF8

#Format -XML -InputObject [xml]($changeText)

#out-file -filepath $filepath -inputobject $changeText -encoding Ascii

write -host "Logic completed"

}

sudhanshu sharma Do good and cast it into river :)

_{Friday, June 24, 2016 10:43 AM}

Your request is far to vague. No one really understands what you are asking. Provide an clear properly formatted example of you XML and a clear indication of what you need to do with it.

\(ツ)_/

_{Friday, June 24, 2016 10:50 AM}

<?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">
<Body>
    <ReportItems>
    </ReportItems>
   </Body>
<Width>23.875in</Width>
<Page>
    <PageHeader>
      <Height>0.625in</Height>
      <PrintOnFirstPage>true</PrintOnFirstPage>
      <PrintOnLastPage>true</PrintOnLastPage>
      <ReportItems>
        <Textbox Name="textbox1">
          <CanGrow>true</CanGrow>
          <KeepTogether>true</KeepTogether>
          <Paragraphs>
            <Paragraph>
              <TextRuns>
                <TextRun>
                  <Value>Employee Participation</Value>
                  <Style>
                    <FontSize>12pt</FontSize>
                    <FontWeight>Bold</FontWeight>
                    <Color>Maroon</Color>
                  </Style>
                </TextRun>
              </TextRuns>
              <Style />
            </Paragraph>
          </Paragraphs>
          <rd:DefaultName>textbox1</rd:DefaultName>
          <Top>0.125in</Top>
          <Height>0.25in</Height>
          <Width>3.25in</Width>
          <Style>
            <PaddingLeft>2pt</PaddingLeft>
            <PaddingRight>2pt</PaddingRight>
            <PaddingTop>2pt</PaddingTop>
            <PaddingBottom>2pt</PaddingBottom>
          </Style>
        </Textbox>
        <Line Name="line2">
          <Top>0.5in</Top>
          <Height>0in</Height>
          <Width>22.875in</Width>
          <ZIndex>1</ZIndex>
          <Style>
            <Border>
              <Style>Solid</Style>
            </Border>
          </Style>
        </Line>
      </ReportItems>
      <Style />
    </PageHeader>
    <PageFooter>
      <Height>0.625in</Height>
      <PrintOnFirstPage>true</PrintOnFirstPage>
      <PrintOnLastPage>true</PrintOnLastPage>
      <ReportItems>
        <Textbox Name="txt_Copyright">
          <CanGrow>true</CanGrow>
          <KeepTogether>true</KeepTogether>
          <Paragraphs>
            <Paragraph>
              <TextRuns>
                <TextRun>
                  <Value>Copyright 2009 Booz & Company Inc. All rights reserved</Value>
                  <Style>
                    <FontSize>8pt</FontSize>
                    <FontWeight>Bold</FontWeight>
                    <Color>SlateGray</Color>
                  </Style>
                </TextRun>
              </TextRuns>
              <Style />
            </Paragraph>
          </Paragraphs>
          <Top>0.25in</Top>
          <Height>0.25in</Height>
          <Width>3.5in</Width>
          <Style>
            <PaddingLeft>2pt</PaddingLeft>
            <PaddingRight>2pt</PaddingRight>
            <PaddingTop>2pt</PaddingTop>
            <PaddingBottom>2pt</PaddingBottom>
          </Style>
        </Textbox>
        <Line Name="line1">
          <Top>0.125in</Top>
          <Height>0in</Height>
          <Width>22.875in</Width>
          <ZIndex>1</ZIndex>
          <Style>
            <Border>
              <Style>Solid</Style>
            </Border>
          </Style>
        </Line>
        <Textbox Name="txt_PageNumber">
          <CanGrow>true</CanGrow>
          <KeepTogether>true</KeepTogether>
          <Paragraphs>
            <Paragraph>
              <TextRuns>
                <TextRun>
                  <Value>="Page " & Globals!PageNumber & " of " & Globals!TotalPages</Value>
                  <Style>
                    <FontSize>8pt</FontSize>
                    <FontWeight>Bold</FontWeight>
                  </Style>
                </TextRun>
              </TextRuns>
              <Style>
                <TextAlign>Right</TextAlign>
              </Style>
            </Paragraph>
          </Paragraphs>
          <Top>0.25in</Top>
          <Left>19.375in</Left>
          <Height>0.25in</Height>
          <Width>3.5in</Width>
          <ZIndex>2</ZIndex>
          <Style>
            <PaddingLeft>2pt</PaddingLeft>
            <PaddingRight>2pt</PaddingRight>
            <PaddingTop>2pt</PaddingTop>
            <PaddingBottom>2pt</PaddingBottom>
          </Style>
        </Textbox>
      </ReportItems>
      <Style />
    </PageFooter>
    <LeftMargin>1in</LeftMargin>
    <RightMargin>1in</RightMargin>
    <TopMargin>1in</TopMargin>
    <BottomMargin>1in</BottomMargin>
    <Style />
</Page>
<AutoRefresh>0</AutoRefresh>
<DataSources>
    <DataSource Name="DS_Records">
      <DataSourceReference>DS_Records</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>8853a0f4-6074-4fee-8c17-225e504ec999</rd:DataSourceID>
    </DataSource>
    <DataSource Name="DS_BoozKM">
      <DataSourceReference>DS_BoozKM</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>e1adbf19-0391-4a94-8f3c-0dbda6ab46de</rd:DataSourceID>
    </DataSource>
</DataSources>
<DataSets>
    <DataSet Name="DS_Records">
      <Query>
        <DataSourceName>DS_Records</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="EmployeeID">
            <Value>=Parameters!EmployeeID.Value</Value>
          </QueryParameter>


        </QueryParameters>
        <CommandText><?xml version="1.0" encoding="utf-8"?>
<root xmlns="http://enesyssoftware.com/schemas">
<sqlData tableName="sqlDataActiveEmployees">
    <providerName>System.Data.SqlClient</providerName>
    <connectionString>Data Source=DEGSQLDWVA23\ONECLICK_D_01;Initial Catalog=BoozKM;Persist Security Info=True;User ID=boozKMAppSvc;Password=3HBt9SPcoqEU</connectionString>
    <commandText><![CDATA[exec dbo.sp_GetEmployeeParticipation @MEUsersOnly!\]]></commandText>
</sqlData>
<sqlData tableName="sqlDataResumes">
    <providerName>System.Data.SqlClient</providerName>
    <connectionString>Data Source=DEGSQLDWVA23\ONECLICK_D_01;Initial Catalog=BoozKM;Persist Security Info=True;User ID=boozKMAppSvc;Password=3HBt9SPcoqEU</connectionString>
    <commandText><![CDATA[SELECT COUNT(URL) AS RESUMESCOUNT, UserID AS EMPID,CONVERT(VARCHAR(10),MAX(LASTMODIFIEDDATE),101) AS LASTMODIFIEDDATE FROM SSRSRESUMES GROUP BY SUBSTRING(URL,11,6), Userid]]></commandText>
</sqlData>
<sqlData tableName="SQLDataICQualsMetadata">
    <providerName>System.Data.SqlClient</providerName>
    <connectionString>Data Source=DEGSQLDWVA23\ONECLICK_D_01;Initial Catalog="KM Portal Usage Logs";Persist Security Info=True;User ID=boozKMAppSvc;Password=3HBt9SPcoqEU</connectionString>
    <commandText><![CDATA[SELECT [ListItemID] as ID
      ,[CreatedDate] as Created
      ,[CreatedBy] as [Created By]
      ,[BoozAuthors] as [AUTHOR(S)]
   , RECORDTYPE
FROM [KM Portal Usage Logs].[dbo].[ICQualsMetadataImport]
WHERE RECORDTYPE = 'IC'
]]></commandText>
</sqlData>
<sqlQuery tableName="DsFinalEmpPrtcpn"><![CDATA[SELECT *,(SELECT RESUMESCOUNT FROM sqlDataResumes WHERE EMPID = EMPLOYEEID) AS RESUMES
, (SELECT LASTMODIFIEDDATE FROM sqlDataResumes WHERE EMPID = EMPLOYEEID) AS RESUMELASTMODIFIEDDATE
, (SELECT COUNT(*) FROM [SQLDataICQualsMetadata] WHERE [CREATED BY] LIKE EMPLOYEENAME || '%' AND RECORDTYPE = 'IC') AS ICPOSTED
, (SELECT COUNT(*) FROM [SQLDataICQualsMetadata] WHERE [AUTHOR(S)] LIKE '%' || EMPLOYEENAME || '%' AND RECORDTYPE = 'IC') AS ICAUTHORED
, (SELECT COUNT(*) FROM [SQLDataICQualsMetadata] WHERE [CREATED BY] LIKE EMPLOYEENAME || '%' AND RECORDTYPE = 'Quals') AS QUALSPOSTED
, (SELECT COUNT(*) FROM [SQLDataICQualsMetadata] WHERE [AUTHOR(S)] LIKE '%' || EMPLOYEENAME || '%' AND RECORDTYPE = 'Quals') AS QUALSAUTHORED
FROM sqlDataActiveEmployees
WHERE (EMPLOYEEID LIKE '%' || @EmployeeID! || '%' OR @EmployeeID! = '')
AND (OFFICE LIKE '%' || @Office! || '%' OR @Office! = 'All')
AND (EMPLOYEENAME LIKE '%' || @EmployeeName! || '%' OR @EmployeeName! = '')
AND (EmpClass LIKE '%' || @EmployeeClass! || '%' OR @EmployeeClass! = '')
AND (PRIMARYALIGNMENT LIKE '%' || @PRALIGNMENT! || '%' OR @PRALIGNMENT! = 'All')
AND (SECONDARYALIGNMENT LIKE '%' || @SECALIGNMENT! || '%' OR @SECALIGNMENT! = 'All')
AND (REGION LIKE '%' || @REGION! || '%' OR @REGION! = 'All')]]></sqlQuery>
<resultSet>DsFinalEmpPrtcpn</resultSet>
</root></CommandText>
      </Query>
      <Fields>
        <Field Name="EMPLOYEEID">
          <DataField>EMPLOYEEID</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>


        <Field Name="Email">
          <DataField>Email</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>

        <Field Name="QUALSAUTHORED">
          <DataField>QUALSAUTHORED</DataField>
          <rd:TypeName>System.Object</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="DsSecondaryAlignment">
      <Query>
        <DataSourceName>DS_BoozKM</DataSourceName>
        <CommandText>SELECT     'All' AS SECONDARYALIGNMENT, 'All' AS ROWNUM
UNION
SELECT DISTINCT SecondaryAlignment, 'xx' AS ROWNUM
FROM         vw_Employees
WHERE     (SecondaryAlignment IS NOT NULL)
ORDER BY ROWNUM</CommandText>
      </Query>
      <Fields>
        <Field Name="SECONDARYALIGNMENT">
          <DataField>SECONDARYALIGNMENT</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="ROWNUM">
          <DataField>ROWNUM</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="DsPrimaryAlignment">
      <Query>
        <DataSourceName>DS_BoozKM</DataSourceName>
        <CommandText>SELECT 'All' as PRIMARYALIGNMENT,'All' AS ROWNUM
UNION
SELECT DISTINCT PRIMARYALIGNMENT,'xx' AS ROWNUM FROM dbo.vw_Employees WHERE PRIMARYALIGNMENT IS NOT NULL ORDER BY ROWNUM</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="PRIMARYALIGNMENT">
          <DataField>PRIMARYALIGNMENT</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="ROWNUM">
          <DataField>ROWNUM</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="DsOfficeLocation">
      <Query>
        <DataSourceName>DS_BoozKM</DataSourceName>
        <CommandText>SELECT 'All' as OFFICELOCATION,'All' AS ROWNUM
UNION
SELECT DISTINCT OFFICELOCATION,'xx' AS ROWNUM FROM dbo.vw_Employees WHERE OFFICELOCATION IS NOT NULL ORDER BY ROWNUM</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="OFFICELOCATION">
          <DataField>OFFICELOCATION</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="ROWNUM">
          <DataField>ROWNUM</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="DsRegion">
      <Query>
        <DataSourceName>DS_BoozKM</DataSourceName>
        <CommandText>SELECT 'All' as REGION,'All' AS ROWNUM
UNION
SELECT DISTINCT REGION,'xx' AS ROWNUM FROM dbo.vw_Employees WHERE REGION IS NOT NULL ORDER BY ROWNUM</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="REGION">
          <DataField>REGION</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="ROWNUM">
          <DataField>ROWNUM</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
</DataSets>
<ReportParameters>
    <ReportParameter Name="EmployeeID">
      <DataType>String</DataType>
      <AllowBlank>true</AllowBlank>
      <Prompt>Employee ID:</Prompt>
    </ReportParameter>
    <ReportParameter Name="EmployeeName">
      <DataType>String</DataType>
      <AllowBlank>true</AllowBlank>
      <Prompt>Employee Name:</Prompt>
    </ReportParameter>
    <ReportParameter Name="EmployeeClass">
      <DataType>String</DataType>
      <AllowBlank>true</AllowBlank>
      <Prompt>Employee Class:</Prompt>
    </ReportParameter>
    <ReportParameter Name="Office">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>DsOfficeLocation</DataSetName>
          <ValueField>OFFICELOCATION</ValueField>
        </DataSetReference>
      </DefaultValue>
      <AllowBlank>true</AllowBlank>
      <Prompt>Office:</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>DsOfficeLocation</DataSetName>
          <ValueField>OFFICELOCATION</ValueField>
          <LabelField>OFFICELOCATION</LabelField>
        </DataSetReference>
      </ValidValues>
    </ReportParameter>
    <ReportParameter Name="REGION">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>DsRegion</DataSetName>
          <ValueField>REGION</ValueField>
        </DataSetReference>
      </DefaultValue>
      <AllowBlank>true</AllowBlank>
      <Prompt>Region:</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>DsRegion</DataSetName>
          <ValueField>REGION</ValueField>
          <LabelField>REGION</LabelField>
        </DataSetReference>
      </ValidValues>
    </ReportParameter>
    <ReportParameter Name="MEUsersOnly">
      <DataType>Boolean</DataType>
      <DefaultValue>
        <Values>
          <Value>false</Value>
        </Values>
      </DefaultValue>
      <Prompt>Management Engineers Only:</Prompt>
    </ReportParameter>
    <ReportParameter Name="PRALIGNMENT">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>DsPrimaryAlignment</DataSetName>
          <ValueField>PRIMARYALIGNMENT</ValueField>
        </DataSetReference>
      </DefaultValue>
      <AllowBlank>true</AllowBlank>
      <Prompt>Primary Alignment:</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>DsPrimaryAlignment</DataSetName>
          <ValueField>PRIMARYALIGNMENT</ValueField>
          <LabelField>PRIMARYALIGNMENT</LabelField>
        </DataSetReference>
      </ValidValues>
    </ReportParameter>
    <ReportParameter Name="SECALIGNMENT">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>DsSecondaryAlignment</DataSetName>
          <ValueField>SECONDARYALIGNMENT</ValueField>
        </DataSetReference>
      </DefaultValue>
      <AllowBlank>true</AllowBlank>
      <Prompt>Secondary Alignment:</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>DsSecondaryAlignment</DataSetName>
          <ValueField>SECONDARYALIGNMENT</ValueField>
          <LabelField>SECONDARYALIGNMENT</LabelField>
        </DataSetReference>
      </ValidValues>
    </ReportParameter>
</ReportParameters>
<Language>en</Language>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportID>94933ca3-3094-46e3-93eb-ed9e226327c0</rd:ReportID>
</Report>

_{Friday, June 24, 2016 11:00 AM}

Hello,

I am taking input a .rdl file as string then finding some key replacing it with new value and saving back in existing file.

After saving when I open my file I see extra nodes like <object> and no proper xml formatting at all.

You can see my code above what I did. I want to know if I can resolve this xml formatting issue also no extra <object node> etc..

If still it is not clear please let me know.

sudhanshu sharma Do good and cast it into river :)

are you a technician or just an end user. DO you know what XML is? You need to load the RDL as XML to edit strings inside the file. You also may not be able to change many elements of an RDL depending on the design. If properly built then most elements can be changed assuming you understand the data source for the report.

Post a copy of the RDL. You can delete all elements within the report but leave header and report wrapper and element you want to edit. I can show you how to do this two or three lines if you are technically capable.

\(ツ)_/

_{Friday, June 24, 2016 11:35 AM}

Note that to edit XML you must use XML methods or you wil break the file:

It is very unclear what you need to edit.

Here is a simple example

[xml]$xml=Get-Content report.rdl
$xml.Report.Datasets.Dataset.Query.CommandText | Out-File MyQuery.txt

\(ツ)_/

_{Friday, June 24, 2016 11:36 AM}

sudhanshu sharma Do good and cast it into river :)

You failed to say what node you want changed or saved.

\(ツ)_/

_{Friday, June 24, 2016 11:41 AM}

Is this what you are trying to change?

<connectionString>Data Source=DEGSQLDWVA23\ONECLICK_D_01

\(ツ)_/

_{Friday, June 24, 2016 11:45 AM}

Posting XML copied from IE is a total waste of time.

I think I will let you sort his out on your own as this si now becoming you dumping things based on no technical knowledge.

Sorry but I have no patience with this today. Someone else will have to sort you out.

\(ツ)_/

Share via

powershell script help...in replacing string from a xml file which I am reading as string

Question

All replies (11)

Additional resources