In my current project, I was presented with strong arguments why InfoPath is a good solution for quickly developing something and getting it out in front of users. Well, the first question I asked, how do you go about writing reports? . And the answer was, well we didn't really had that requirement hence we didn't think about it. And believe it or not, 5 weeks into this project and a user group wants to see InfoPath forms(hosted on SPS 2003 hence InfoPath 2003) data in a nice reporting services report. hmm! My worst nightmare came true . Here is the synopsis of what exactly need to happen.
1. 'Get' xml files from InfoPath forms library to a shared file location.
2. Read the xml file one by one and populate database tables.
3. Create a Reporting Services Report against accumulated data.
In my today's post, I will walk you through the process of 'getting' xml files from the SharePoint forms library. I keep saying xml files because, InfoPath stores its data in .xml format (InfoPath 2007 & MOSS forms server, it is a different story). Since the requirement is to run the utility outside of SharePoint server, I didn't had the luxury of working against SharePoint Object Model. But, we do have web services that can help get the xml files out of the system.
Start out by creating a new C# Console application in VS 2005. After that add a web reference to your project and point it to the SharePoint forms library. If the URL of your site is http://sps/corporate/sites/mysite then add web reference as http://sps/corporate/sites/mysite/_vti_bin/Lists.asmx. Once the reference is set you make a connection to your webservice using the following line.
MyWebReference.Lists wsList = new MyReference.Lists();
If your SPS site authenticates users against Active Directory, then you want to impersonate your wsList object. The following two lines are extremely important without which you will not be able to code against SharePoint lists.
wsList.PreAuthenticate = true;
wsList.Credentials = System.Net.CredentialCache.DefaultCredentials;
After this, you must have permissions to query against the SharePoint lists. The following code create XML object and in a for loop calls another function which use HTTPWebRequest object to get the XML file and dump it in a network shared folder.
XmlNode allLists = wsList.GetListCollection();
XmlDocument allListsDoc = new XmlDocument();
allListsDoc.LoadXml(allLists.OuterXml);
XmlNamespaceManager ns = new XmlNamespaceManager(allListsDoc.NameTable);
ns.AddNamespace("d", allLists.NamespaceURI);
XmlNode dlNode = allListsDoc.SelectSingleNode("/d:Lists/d:List[@Title='" + documentLibraryName + "']", ns);
if (dlNode == null)
{
Console.WriteLine("Document Library '{0}' not found!", documentLibraryName);
}
else
{
string documentLibraryGUID = dlNode.Attributes["ID"].Value;
Console.WriteLine("Opening folder '{0}' GUID={1}", documentLibraryName, documentLibraryGUID);
XmlDocument viewFieldsDoc = new XmlDocument();
XmlNode listContent = wsList.GetListItems(documentLibraryGUID, null, null, null, null, null);
XmlDocument xmlResultsDoc = new XmlDocument();
xmlResultsDoc.LoadXml(listContent.OuterXml);
ns = new XmlNamespaceManager(xmlResultsDoc.NameTable);
ns.AddNamespace("z", "#RowsetSchema");
XmlNodeList rows = xmlResultsDoc.SelectNodes("//z:row", ns);
string sFileName = string.Empty;
if (rows.Count == 0)
{
Console.WriteLine("No content found");
}
int iCounter = 0; ;
if (!RecycleExtracts()) { throw new InvalidProgramException("Recycle of records failed: ");}
foreach (XmlNode row in rows)
{
sFileName = rows[iCounter].Attributes[1].Value; // Get file Name
Extract(sFileName);
iCounter++;
}
}
And here is the Extract function:
private static void Extract(string sFileName)
{
string httpLocation = FormsLibraryLocation + sFileName;
string extractPath = GetAppDirectory() + sFileName;
try
{
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(httpLocation);
req.Credentials = System.Net.CredentialCache.DefaultCredentials;
req.Timeout = 10000;
req.AllowWriteStreamBuffering = false;
HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
Stream st = resp.GetResponseStream();
StreamReader sr = new StreamReader(st, Encoding.UTF8);
string buffer = sr.ReadToEnd();
File.WriteAllText(extractPath, buffer);
}
catch (Exception ex)
{
Console.WriteLine("Exception while creating " + sFileName + " " +ex.Message);
}
}
That’s it folks. This little piece of code 'gets' the xml files from the SharePoint 2003 Portal Server forms library and create a file with the exact same name on the network shared folder. In my future post, I will write about how to read these xml files and populate in database. Until then, happy coding!