Microsoft cloud engineer - SharePoint, Office 365, Azure, DotNet, Angular, JavaScript.
Microsoft cloud engineer - SharePoint, Office 365, Azure, DotNet, Angular, JavaScript.

December 2013

InfoPathDB – query repeating tables over SOAP, JSON, Excel

I recently published a major update to http://infopathdb.codeplex.com/   which enables users to query InfoPath XML data (including repeating tables) over SOAP, JSON, and Excel.   It’s great for reporting against data collected with InfoPath forms.    Please check out the project and let me know if you have any ideas to improve.  Thanks! 

Hot smile

 

===

Project Description
Aggregate and query multiple InfoPath XML documents from a SharePoint Form Library. Supports SOAP, XML, JSON, and IQY Excel format.

Have you ever wanted to report directly against InfoPath Form Library data? Have you been frustrated by the limitations of published columns? Have you had trouble when trying to report on repeating table (one-to-many) data? Do you like working with clean XML web services? Then download and check out this SOAP web service.

This code will aggregate many source XML documents into a single XML for easy reporting. Because InfoPath documents share a common XSD schema from within the template XSN, they may be consolidated using Dot Net objects like DataSet, DataTable, and XmlDocument.

–UPDATED September 21, 2013–


Features

  • Download to Excel IQY web query
  • XML output
  • JSON output
  • CAML row filter
  • People Picker data with BuiltInActiveXControls.xsd
  • Item level security
  • Both HTTP GET and POST
  • Farm Feature applies IIS virtual directory "Convert to Application" change across the farm (needed for web.config to apply)
  • Site Settings > Site Administration > InfoPathDB custom menu
  • Query builder ASPX page

How It Works

  • Reads SharePoint Form Library XSN template
  • Extracts XSD schema file
  • Reads all XML document files
  • Outputs a single aggregate XML data set
  • Compatible any XML reporting tool (Excel, SQL Reporting Services, Performance Point Services, PowerPivot, InfoPath, etc.)

SharePoint Compatibility

  • Works on 2007
  • Works on 2010
  • Works on 2013
  • Not ready for Office 365 (farm full trust, no sandbox or app)

Usage
There are four parameters to the SOAP method QueryFormLibrary:

  • SiteURL – URL address of the SPWeb hosting the Form Library
  • FormLibraryTitle – Title of the Form Library
  • OptionalContentType – When working with Administrator approved Central Admin XSN templates, you may need to specify a content type. with basic forms, this should be "Form".
  • OptionalCAMLFilter – Where clause filter to reduce Form Library rows. For example, "<Where><Eq><FieldRef Name=’Status’/><Value Type=’CHOICE’>Not Started</Value></Eq></Where>"
  • OptionalBooleanIncludeAttachments – Include large binary attachment fields (base64 data type). By default, these values are skipped over.

Screenshots

Cabinet file extract method from "Iterate and Extract Cabinet File" CodeProject By aplaxas. JSON parsing method from Json.NET. All other trademarks and copyrights are property of their respective owners.
Please drop me a line via email spjeff@spjeff.com or Twitter @spjeff with ideas. I’m always open to suggestions and improvements.

Launch IE as multiple users for testing

When testing role based security, it can be helpful to right click IE and “Run as different user.”   The below PowerShell script automates this process to make it easier to launch multiple IE windows for testing.   Keeping a copy on Windows 7/8 workstations can help users jump into testing easily and with fewer password typo lockouts.    If you find this helpful, please leave a comment. 

Smile

 

# Launch multiple IE windows as different test user accounts.  Great for testing role based security systems.
# Updated line 3 with user name, line 6 with Active Directory domain, and line 9 with SharePoint URL.
$users = ('sptest1','sptest2','sptest3','sptest4')
foreach ($u in $users) {
	Write-Host $u
	$username = ('domain\' + $u)
	$password = 'pass@word1'
	$cred = New-Object System.Management.Automation.PSCredential -ArgumentList @($username,(ConvertTo-SecureString -String $password -AsPlainText -Force))
	Start-Process "c:\Program Files\Internet Explorer\iexplore.exe" -LoadUserProfile -Credential $cred -ArgumentList "http://sharepointUrlHere"
}

 

Here is a screenshot of the script in action.

1

Merry Christmas – HTML5, JS, and CSS3

I wanted to thank all of you for reading my blog and wishing you a Happy Holiday season.   After passing exam 070-480 about HTML5, it seemed like a great time to practice new skills and draw a <CANVAS> based Christmas Tree here.   Enjoy!  

Smile

 

Screenshot of what it should look like:

image

Code behind:

 

Live working demo:



Merry Christmas!

Office Web Apps Error 1000 / Dot Net 1026 [FIXED]

I saw the below error when working with Office Web Apps 2013 and SharePoint 2013 in a two farm topology where OWA was installed on the Domain Controller and a separate machine ran SharePoint 2013 and SQL 2012.    OWA here is running the October Cumulative Update hotfix KB 2825686 and SharePoint 2013 is also running the October Cumulative Update KB 2825647.     Windows Updates were run and appear current.

 

The cause appears to be applying a patch to the “OWA farm” (not SharePoint farm).   Re-creating the farm    The PowerShell resolution steps I followed were:

 

 

  • SP machine
    • IISRESET
    • test with IE again

 

Screenshots

1
image
image
image
image
image

 

 

Error Messages

—————–

Application Application Error 1000 Error

Faulting application name: ImagingWatchdog.exe, version: 15.0.4469.1000, time stamp: 0x50c73f9f
Faulting module name: KERNELBASE.dll, version: 6.2.9200.16451, time stamp: 0x50988aa6
Exception code: 0xe0434352
Fault offset: 0x000000000003811c
Faulting process id: 0x1c38
Faulting application start time: 0x01cef04aa7394f28
Faulting application path: C:\Program Files\Microsoft Office Web Apps\ImagingWatchdog\ImagingWatchdog.exe
Faulting module path: C:\Windows\system32\KERNELBASE.dll
Report Id: e6f0072a-5c3d-11e3-9403-0800279e627c
Faulting package full name:
—————–

Application Application Error 1000 Error

Faulting application name: WordViewerAppManagerWatchdog.exe, version: 15.0.4481.1000, time stamp: 0x50ee5a78
Faulting module name: KERNELBASE.dll, version: 6.2.9200.16451, time stamp: 0x50988aa6
Exception code: 0xe0434352
Fault offset: 0x000000000003811c
Faulting process id: 0x1ebc
Faulting application start time: 0x01cef04aa6e37b2a
Faulting application path: C:\Program Files\Microsoft Office Web Apps\WordViewerAppManagerWatchdog\WordViewerAppManagerWatchdog.exe
Faulting module path: C:\Windows\system32\KERNELBASE.dll
Report Id: e6d36927-5c3d-11e3-9403-0800279e627c
Faulting package full name:
Faulting package-relative application ID:

—————–

Application .NET Runtime 1026 Error

Application: ImagingWatchdog.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.TypeInitializationException
Stack:
   at Microsoft.Office.Web.Common.ServiceInstanceFinder.GetLocalAgentInstance(Microsoft.Office.Web.Common.OfficeServiceType)
   at Microsoft.Office.Web.Common.WatchdogHelper.PrepareRegistrations(Microsoft.Office.Web.Common.OfficeServiceType)
   at Microsoft.Office.Web.Common.WatchdogHelper.WatchMachines(Microsoft.Office.Web.Common.OfficeServiceType, CheckServiceInstance, Microsoft.Office.Web.Common.OfficeServiceType, System.String)
   at Microsoft.Office.Web.Watchdogs.Program.Main()

—————–

Application .NET Runtime 1026 Error

Application: WordViewerAppManagerWatchdog.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.TypeInitializationException
Stack:
   at Microsoft.Office.Web.Common.ServiceInstanceFinder.GetLocalAgentInstance(Microsoft.Office.Web.Common.OfficeServiceType)
   at Microsoft.Office.Web.Common.WatchdogHelper.PrepareRegistrations(Microsoft.Office.Web.Common.OfficeServiceType)
   at Microsoft.Office.Web.Common.WatchdogHelper.WatchMachines(Microsoft.Office.Web.Common.OfficeServiceType, CheckServiceInstance, Microsoft.Office.Web.Common.OfficeServiceType, System.String)
   at Microsoft.Office.Web.WordViewerWatchdog.Program.Main()

References

© Copyright 2016
@ SPJeff

Return to Top ▲Return to Top ▲