How to fetch data from XML file and auto-populate the fields in NEWFORM.aspx


Recently a customer came up with a requirement that he is having some structured data in XML file and now when they are creating a list which will have data populated from the XML file.

The XML file contained the machine details for a particular user and XML was structured as:

<data>
    <user>
          <username>Username</username>
          <mac>MAC Address</mac>
          <machine>Machine Name</machine>
    </user>
</data>

The new custom list in SharePoint contained UserName, MAC and Machine Name as fields along with some other fields. Basically was to be used for recording Support Incidents for the machines. Customer wanted that when in NewForm.aspx, UserName field is filled, it should automatically pull-in information from XML file and fill in the MAC and Machine Name fields for the form.

First  thought was to create a new customized list definition (to remain in supported scenario) and use server-side code but that seems to be a little too much of task, just to populate a couple of fields.

Earlier exposure to raw JavaScript programming with XMLHttpRequest (XHR) came in handy here. A simple solution was decided as it was to be done only for 1 list and very specific stuff need to be pulled.

So the story begins, where we would be creating an ASPX page which will be responsible for fetching the XML file and getting the data filtered for the entered username. There would be an XHR request generated by JavaScript which will connect with the ASPX page and pull-in data and populate the fields.

For simplicity, I created a Virtual Directory in IIS, under the _layouts folder of my SharePoint application (named as Users) and hosted a Default.aspx page in it with the following code:

   1: // All the work is being done in the Page_Load event of Default.aspx only
   2: protected void Page_Load(object sender, EventArgs e)
   3: {
   4:     // we will work only if there is a QueryString named as "u"
   5:     if (!string.IsNullOrEmpty(Request.QueryString["u"]))
   6:     {
   7:         // Create the SPSite object
   8:         using (SPSite site = new SPSite(http://MySharePointSite))
   9:         {
  10:             // Get its SPWeb object
  11:             using (SPWeb web = site.OpenWeb())
  12:             {
  13:                 // Get the particular list item which contains the XML file
  14:                 SPListItem item = web.GetListItem(http://MySharePointSite/Shared%20Documents/TestXML.xml);
  15:  
  16:                 // Get the SPFile object from the SPListItem
  17:                 SPFile file = item.File;
  18:  
  19:                 // Get the file contents as Byte array 
  20:                 byte[] b = file.OpenBinary();
  21:  
  22:                 // Convert the byte array to string
  23:                 string s = (new System.Text.ASCIIEncoding()).GetString(b);
  24:  
  25:                 // XML structure is like:
  26:                 // <data>
  27:                 //     <user>
  28:                 //         <username>User name comes here</username>
  29:                 //         <mac>MAC address will be here</mac>
  30:                 //         <machine>Machine name will be here</machine>
  31:                 //     </user>
  32:                 //     <user>
  33:                 //         ... similarly for other users also ...
  34:                 //     </user>
  35:                 // </data>
  36:  
  37:                 // Create new XmlDocument object and load the file contents
  38:                 XmlDocument doc = new XmlDocument();
  39:                 doc.LoadXml(s);
  40:  
  41:                 // Select only the "user" nodes from the data
  42:                 XmlNodeList nodes = doc.SelectNodes("/data/user");
  43:  
  44:  
  45:                 // Loop through each of the "user" nodes in XML
  46:                 foreach (XmlNode node in nodes)
  47:                 {
  48:  
  49:                     // iterate only if we have data inside the User node
  50:                     if (node.HasChildNodes)
  51:                     {
  52:  
  53:                         // check if we have the "username" tag matching with our QueryString
  54:                         if(node.FirstChild.InnerText.ToLower().Equals(Request.QueryString["u"].ToLower()))
  55:                         {
  56:  
  57:                             // yes, we found it. Now dump out the data in delimited format
  58:                             // The output response will be like the following (separated by "|" [a pipe symbol])
  59:                             // UserName|MAC Address|Machine Name
  60:                             Response.Write(string.Format("{0}|{1}|{2}",node.ChildNodes[0].InnerText,node.ChildNodes[1].InnerText,node.ChildNodes[2].InnerText));
  61:  
  62:                             // we found our data, so break from the loop
  63:                             break;
  64:                         }
  65:                     }
  66:                 }
  67:  
  68:                 // We do not want to dump out anything else, so end the response stream
  69:                 Response.End();
  70:             }
  71:         }
  72:     }
  73: }
  74:  


Now over to the client part.

I opened up the NewForm.aspx page in SharePoint Designer and added a new Content Editor Web Part to add the JavaScript required for the client side operation. In the list I had the following names of the fields:

1. UserName

2. MAC

3. Machine

Now over to final creation of JavaScript code and here is the code I had used:

   1: <script language="javascript">
   2:  
   3: // used for displaying the "loading..." message
   4: var loadingDIV = null;
   5:  
   6: // This will hold reference to UserName control
   7: var ctlUser = null;
   8:  
   9: // This will hold reference to MAC Address control
  10: var ctlMAC = null;
  11:  
  12: // This will hold reference to Machine Name control
  13: var ctlMachine = null;
  14:  
  15: function f()
  16: {
  17:       // Create a new XHR request (this will work only with browsers with native XHR support)
  18:       // for other browsers like IE6 or lower, you would need to change it to create XMLHttpRequest ActiveX object
  19:       var mygetrequest = new XMLHttpRequest();
  20:  
  21:       // Here is where we do our work, once data is received back
  22:       mygetrequest.onreadystatechange = function() 
  23:       {
  24:         // Check readyState of the request
  25:         if (mygetrequest.readyState == 4) 
  26:         {
  27:           // Check if we have the proper request status
  28:           if (mygetrequest.status == 200)
  29:           {
  30:               // get the mData from the request. This data comes as "Username|MAC|MachineName" string
  31:               // separated using a "|" (pipe) sign
  32:               var mData = mygetrequest.responseText;
  33:  
  34:               // split the data to get the string array
  35:               ar = mData.split("|");
  36:  
  37:               // MAC address is the 2nd element in the array, assign value to the MAC textbox
  38:               ctlMAC.value = ar[1];
  39:  
  40:               // Machine name is 3rd element in the array, assign value to Machine name textbox
  41:               ctlMachine.value = ar[2];
  42:  
  43:               // hide the "loading..." message as we have loaded the data properly
  44:               loadingDIV.style.display="none";
  45:           }
  46:           else 
  47:           {
  48:              alert("An error has occured making the request")
  49:           }
  50:        }
  51:     }
  52:     
  53:     // Show the "loading..." DIV to show that we are fetching the data
  54:     loadingDIV.style.display="inline";
  55:  
  56:     // Send the actual request to our ASPX page which will read the XML file and give us data back
  57:     mygetrequest.open("GET", "/_layouts/Users/Default.aspx?u=" + ctlUser.value, true)
  58:     mygetrequest.send(null)
  59: }
  60:  
  61: // Fetch all the elements of tag as INPUT (we just need textbox references)
  62: var ele = document.getElementsByTagName("input");
  63:  
  64: // Loop through all the INPUT elements
  65: for (a = 0; a < ele.length; a++) 
  66: {
  67:       try 
  68:       {
  69:             ix = ele[a];
  70:  
  71:             // We need only the TEXT type INPUT elements
  72:             if (ix.type == "text") 
  73:             {
  74:  
  75:                   // SharePoint adds a TITLE attribute to each textbox which is exactly same as its Field Name
  76:                   // Check if we found "USERNAME" field here
  77:                   if (ix.attributes.getNamedItem("title").value.toLowerCase() == "username") 
  78:                   {
  79:                         // Set the UserName control object reference
  80:                         ctlUser = ix;
  81:  
  82:                         // Set the onBlur event handler to point to function "f()" which will do the actual work
  83:                         ctlUser.onblur = f;
  84:  
  85:                         // Now create a new SPAN element which contains the "loading..." message 
  86:                         // and take its reference in "loadingDIV" and inset it after the UserName textbox
  87:                         var xx = document.createElement("span");
  88:                         xx.innerHTML = "loading...";
  89:                         xx.id="loadingBlock";
  90:                         xx.style.display = "none";
  91:                         loadingDIV = xx;
  92:                         ctlUser.insertAdjacentElement("afterEnd", xx);
  93:                   }
  94:  
  95:                   // If this TEXT type INPUT element is MAC address, take its reference
  96:                   if (ix.attributes.getNamedItem("title").value.toLowerCase() == "mac") 
  97:                   {
  98:                         ctlMAC = ix;
  99:                   }
 100:  
 101:  
 102:                   // If this TEXT type INPUT element is Machine name, take its reference
 103:                   if (ix.attributes.getNamedItem("title").value.toLowerCase() == "machine") 
 104:                   {
 105:                         ctlMachine = ix;
 106:                   }
 107:             }
 108:       }
 109:       catch (ex)
 110:       { 
 111:           //Right now we are not doing anything if any error occurs
 112:       }
 113: }
 114: </script>

 

So basically in the JavaScript code, we are:

1. Iterating through all the INPUT tags of type TEXT

2. Check if TITLE attribute of the tag match any of our fields, we store a reference of them.

3. Attach an event hander for onBlur for our UserName text field, which calls the function “f()”

4. When the function “f()” is called, it initiate the XHR request to fetch the pipe delimited data from our ASPX file and then split the data and fill in the MAC and Machine Name text fields.

 

Hope some of you will find it handy and use it in your projects.

Happy Coding…

Skip to main content