Open xml from Sharepoint document library in powershell

Question

I'm trying to manipulate a xml file from sharepoint document library with powershell. Unfortunately I cannot open it and cast it to xml. :( I have same file in the document library and locally on hard drive. When I open it from hard drive everything is fine. When I open it from Sharepoint Document Library the cast to xml fails because of an extra character in the beginning of the file. I binary compared the result from $splistitem.File.OpenBinary() and from get-content C:....\file.xml and its the same. The problem is with getting the string from bytes. I tried all available encodings, but nothing worked. Here is my code:

   # Add SharePoint snapin if needed
    if ((Get-PSSnapin -Name Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue)     -eq $null)
    {
        Add-PSSnapin Microsoft.SharePoint.Powershell
    }

...
    $splistitem = ...
...

    $encode = New-Object System.Text.UTF8Encoding

    [xml]$xmlFromSharepoint = $encode.GetString($splistitem.File.OpenBinary()) #throws exception
    [xml]$xmlFromFile = get-content C:\....\file.xml #works fine

    $web.Dispose()

    Write-Host "Press any key to close ..."
    $x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

The exception thrown is with message:

        Cannot convert value "?<?xml version="1.0" encoding="utf-8"?> 
..." to type "System.Xml.XmlDocument". Error: "Data at the
    root level is invalid. Line 1, position 1."
    At C:\aaa.ps1:14 char:24
    + [xml]$xmlFromSharepoint <<<<  = $encode.GetString($splistitem.File.OpenBinary
    ())
        + CategoryInfo          : MetadataError: (:) [], ArgumentTransformationMet
       adataException
        + FullyQualifiedErrorId : RuntimeException

Ivan Ivan · Accepted Answer · 2012-11-16T10:52:11

I'm answering myself. :)

The character infront of file is a byte order mark for UTF8 encoding. I found that I can load the xml file directly from sharepoint stream. This sloves the problem. My goal was to chages the connection string for Reporting Services datasource in sharepoint library. The .rsds file is a normal xml file which holds the connection string. Here is the code to modify it:

# Add SharePoint snapin if needed
if ((Get-PSSnapin -Name Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue)     -eq $null)
{
    Add-PSSnapin Microsoft.SharePoint.Powershell
}

#Configure Connection strings
$web = Get-SPWeb "http://localhost/c1"
$reportsList = $web.Lists | Where-Object { $_.Title -eq "Reports" }
$dataSource = $reportsList.Items | Where-Object { $_.Name -eq "Datasource.rsds" }
$dataSource.File.CheckOut();
$xml = New-Object System.Xml.XmlDocument
$stream = $dataSource.File.OpenBinaryStream();
$xml.Load($stream)
$xml.DataSourceDefinition.ConnectString = "test"
$stream.Position = 0;
$stream.SetLength(0);
$xml.Save($stream);
$dataSource.File.SaveBinary($stream)
$dataSource.File.CheckIn("");
$web.Dispose()

Write-Host "Press any key to continue ..."
$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

Actually I realized that the above code doesn’t work. To modify reports I have to use the ReportServer web service. Here is now the working code:

function ConfigureConnectionString
{
param($webUrl, $connectionString)
    $reportServerURI = "http://$serverUrl/ReportServer"
    $ReportPathWildCard = "/";
    $NameSharedSchedule="NeverExpireCache";

    $reportServerURI2010 = "$reportServerURI/ReportService2010.asmx?WSDL"
    $RS = New-WebServiceProxy -Class 'RS' -NameSpace 'RS' -Uri $reportServerURI2010 -UseDefaultCredential
    $dataSources = $RS.ListChildren($ReportPathWildCard,$true) | Where-Object {$_.TypeName -eq "DataSource" -and $_.Path -like "*$webUrl*"}
    foreach($ds in $dataSources) {
        $dsDefinition = $RS.GetDataSourceContents($ds.Path)

        $dsDefinition.CredentialRetrieval = "Store"
        $dsDefinition.Enabled = "TRUE";
        $dsDefinition.UserName = "domain\user";
        $dsDefinition.Password = "Password";
        $dsDefinition.ConnectString = $connectionString;

        #Update the DataSource with this new content
        $RS.SetDataSourceContents($ds.Path, $dsDefinition);
    }
}

Open xml from Sharepoint document library in powershell

1 Answers