This project has moved and is read-only. For the latest updates, please go here.

GetListItems, XMLtoJSON, and jQueryUI autocomplete

Sep 26, 2012 at 10:20 PM
Edited Sep 26, 2012 at 11:16 PM

I don't really have a question per se, I'm just trying to get some ideas.  I've got a pages library that will hold news articles.  As part of the content type for this library, there is a lookup column pointing to a Tag list where authors can specify tags. No issues there.

I'm recreating an interface to surface this content using jQuery and SPServices.  The existing managed code solution uses a search/filter mechanism with auto complete powered by jQuery UI. So as you type for Books, it might show Bobsled (1), Books (15), Boston (3).  I'd like to replicate this if possible.

My quandry comes in as to how to make the multiselect Tag data from my pages library useable by jQuery autocomplete. I'd have to get all pages, parse each items tag(s), evaluate if it already exists and eiter add it to an array or increment a counter in the array. The array or json would end up looking like [ { label: "Bobsled (1)", value: "Bobsled" },{ label: "Books (15)", value: "Books" },{ label: "Boston (3)", value: "Boston" } ... ] so that the label would be what is displayed in the jQuery Autocomplete, but the value is what I hand off to SPServices to fetch items based on the input.

Is a multideminsional array the choice here or would making this a JSON return be the route?  The ghetto approach in my mind is to parse the tags on item creation and update a counter variable in the tag lookup list and just fetch that but that feels clunky. There'd need to be more logic to store previous tags to account if the item is retagged. I'm thinking that fetching and caching it is the proper choice.

Open to any ideas or tips if you've encountered this in the past.

Sep 27, 2012 at 1:42 AM


The json object is the desired approach in my mind, being that you are using jQuery UI. If you don't have a way to keep a counter on the Tag list for each tag, automatically as the user tags and un-tags, then you can either generate it on the client side (javascript/SPServices) or maybe with SPDesigner and thus "server side at runtime. Here are a few ideas:

Javascript - Generate the JSON Object on the browser:

This could be time consuming depending on 1) number of tags and 2) number many articles. Building should be a no-brainer: loop through each Tag and do a GetListItems on that tag and store the amount of records found. The downside is the time this will take, which will prevent you from displaying the UI to your user (not ready yet).

There are ways you can sacrifice the wait time of one user for the good of all: once you generate the json object the first time, store it in list. Then re-use that already create json object if it is not older than (for example) 10 minutes... or better yet.... if it is not older than the date/time of the last time a page was touched (in your pages library)... you can quickly find that out by (I think) checking the Document Library "list" level attributes (GetList) or doing a GetListItems from the Pages library (remember to do recursive) for any page whose modification date/time is greater than the last time you stored the json data.

I use this approach of "caching" across a site today (even across multiple sites under the same farm)... Yes the user that actually triggers a rebuild of data does experience a longer delay... but helps everyone else, including that user when he comes back to the page... and... (hehehe) it is unlikely that user will complain because he will not be able to recreate on demand.  I use a "hidden" table called ptAppConfig with a few columns of different types, case I need to store different type of data (like HTML for template snippets). The Title column is used as a unique key, which ensure I don't duplicate through out my applications.


SPDesigner / Workflow - Store totals on each Tag:

Perhaps you can devise a way to update the tags each time a Page is Tagged or un-tagged by keeping the value updated in the Tag's list... As you mentioned, this is a little getto... but could work... I'm not sure how to do it via a Workflow (I currently do this via a workflow but not for a field that is Multi-Select)... On the client (on the NewForm, EditForm pages) this could be done by intercepting the "OK" button and updating the Tag Number first then allowing the update to go through...

Overall, this approach might be troublesome because of deadlock contention: what if two or more user go after the updat at hte same time? When done client side, you would have to first retrieve the tag's row to get the current "count" then +1 and save it back.. in that same time another update could have gotten in there..


SPDesigner - Generate the json on the server:

Maybe use SP Designer to build a webpart that builds your json object at runtime and inserts it into the page. Like that it will be available on the client side when the page is rendered. I am not sure if this is possible (now that I think about the steps that would need to happen) via SP designer.


Hope this helps.



(example: I usually keep a "hidden" list called ptAppConfig with a few columns of different types, case I need to store differnt type of data (like HTML for template snippets)
Sep 27, 2012 at 2:56 AM

Thanks Paul.

There will be thousands of pages with maybe upwards of 5 to 6 tags each, so building it on demand for each user will be a bottle neck.  However, this might not be apparent to the user if constructed right as it can be fetching, processing, and caching the results in the background. Users would be perusing the most recent news feed while this is going on so by the time they want to search/filter with the autocomplete it might appear to function as normal.

If I went the workflow route, I could easily schedule something (with Nintex eventually) to routinely query the library, collect the tags, and update a list holding the data like you mentioned.

I hadn't really thought about a data view web part option, I'll have to think about that more.

Sep 27, 2012 at 3:06 AM
Post back when you nail down your design.


Sent from mobile device.
Oct 13, 2012 at 2:41 PM
Did you lock down a solution? I'm curious because:

I have recently came across the need to retrieve data that was a merge of two Lists. Using client side code alone will be expensive, so I'm looking into a data view webpart that actually does the combine view and it returns json. I have it working to some extent already, but for some reason jquery's parseJSON is not liking something so I'm debugging.


Paul T

Oct 14, 2012 at 5:35 PM

I'm going in an alternate direction, instead of trying to iterate over all the tags and getting a count, I'm using some CSOM code to retrieve all of the terms in the term store and returning that as an array.  I'm still running into issues with Autocomplete picking it up and using it.

Oct 15, 2012 at 4:32 AM


Josh has a post that would be a good start for you. Take a look:


Oct 16, 2012 at 2:36 AM

Eric, Matt,

Here is what I'm using in a Data View webpart... It generates a json object ready to be consumed on the client side. In my usage, I wanted the data to be provided back as txt and not evaluated to a javascript object... the reason being I did not want to take up memory with large data structure until I actuality need it. I have used this to dump out entire list content, even those that have fields of type "Rich Text" (html) with no issues (thus far).

So the xslt below writes the json data as text to a script tag that is set to type text/plain (note the type attribute).  Then, when appropriate on the browser, I read it as:


var data = $.parseJSON( $("#jsonData").html() );


XSLT template is:


<xsl:stylesheet xmlns:x="" xmlns:d="" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="" xmlns:asp="" xmlns:__designer="" xmlns:xsl="" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
    <xsl:output method="html" indent="no"/>
    <xsl:decimal-format NaN=""/>
    <xsl:param name="dvt_apos">'</xsl:param>
    <xsl:param name="dvt_firstrow">1</xsl:param>
    <xsl:param name="dvt_nextpagedata" />
    <xsl:variable name="dvt_1_automode">0</xsl:variable>
     <xsl:template match="/"
        <xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row"/>
        <div class="jsonData">
        <script type="text/plain" id="jsonData">
        <xsl:text>{"data": [</xsl:text>
        <xsl:if test="count($Rows)">
            <xsl:for-each select="$Rows">
                <xsl:variable name="thisRowAttr" select="@*" />
                <xsl:for-each select="$thisRowAttr">
                    <xsl:value-of select="name()" />
                                <xsl:variable name="noQuotes">
                                                <xsl:call-template name="doTextReplace">
                                                                <xsl:with-param name="textValue" select="." />
                                                                <xsl:with-param name="findValue">&quot;</xsl:with-param>
                                                                <xsl:with-param name="replaceValue">\&quot;</xsl:with-param>
                                <xsl:variable name="noNewLineBreaks">
                                                <xsl:call-template name="doTextReplace">
                                                                <xsl:with-param name="textValue" select="$noQuotes" />
                                                                <xsl:with-param name="findValue"><xsl:text>&#13;&#10;</xsl:text></xsl:with-param>
                                                                <xsl:with-param name="replaceValue">
                        <xsl:value-of select="$noNewLineBreaks" />
                                <xsl:if test="count($thisRowAttr) != position()">
                <xsl:if test="count($Rows) != position()">
        </script> </div>
    <xsl:template name="doTextReplace">
        <xsl:param name="textValue" />
        <xsl:param name="findValue" />
        <xsl:param name="replaceValue" />
        <xsl:variable name="first" select="substring-before($textValue, $findValue)" /> 
        <xsl:variable name="remaining" select="substring-after($textValue, $findValue)" />
            <!-- textValue was empty... return nothing. -->
            <xsl:when test="not(string-length($textValue))"> 
            <!-- textValue did not contain the findValue... Return it. -->
            <xsl:when test="not(contains($textValue, $findValue))">
                <xsl:value-of select="$textValue" />
                <xsl:value-of select="$first" />
                <xsl:value-of select="$replaceValue" />
        <xsl:if test="$remaining">
            <xsl:call-template name="doTextReplace">
                <xsl:with-param name="textValue" select="$remaining" /> 
                <xsl:with-param name="findValue" select="$findValue" />
                <xsl:with-param name="replaceValue" select="$replaceValue" />



Like I said earlier, I use this mostly when I need to get data that combines content from two lists.  I will also be using it soon (new project) to calculate some metrics on the server based on some list content, and send back only the metrics (ex. {Failed: 20, Passed: 30})

Hope this helps (now or in the future).


ps. the doTextReplace <xsl:template> above can actually be used for anything.. Its generic sna I used it today client side to manipulate dats for presentation (ex. to break up a list of Users on People field that has multiple users .


Oct 16, 2012 at 11:55 PM
Edited Oct 17, 2012 at 12:24 AM

Interesting, I'll have to keep this in mind and experiment with it. As always it's a fine line between balancing client side and server side processing.

This wasn't too difficult to set up as a linked DVWP. My issue now for a different use case in the same project is to make a JSON object like this out of 1 master list and 2 child lists. A lof of XSLT to sort through but it looks promising.

Oct 17, 2012 at 7:16 PM

I really want to use this, but in this project, the data has to be flexible. So I have one SPServices call with 4 CAMLQuery options to pull data depending on what conditions exist.  To make this work, I'd need 4 separate DVWPs to output different data as the DVWPs are not flexible in that sense (dynamic number of records to return, filter criteria, etc).

I'll have to decide if 4 blobs of json data sitting hidden and waiting on my page are a better trade off than making many posts and processing the results on the fly.

Oct 17, 2012 at 8:08 PM

If you want to, you could make use of localstorage. When the request is initially made to the page, async: false the initial request and then the other 3 async: true and cache the xml or whatever you want in localstorage whenever it is returned.



Oct 17, 2012 at 8:36 PM

It's just the age old question, do you make a lot of small requests or fewer large requests.

Oct 17, 2012 at 8:57 PM

A lot... You want the page to load as fast as possible imo.

Oct 18, 2012 at 4:03 AM
Eric, Matt,
You are right in that the 'when to use this approach' will be answered by which method is less expensive. The blobs of text in the page should not increase the size of memory or the DOM tree because they are in stored as text. It should also not impact (too much) the loading of the page because the browser has nothing to parse into DOM. It's not until you turn it into a js object that they are loaded into memory for manipulation.

The instances where I have used this, and similar code, has been when I need data merged from two lists (share a common column). In these cases, it just too long to do it client side. The other was to generate stats (where I would have had to otherwise pull all the data client side) and in one case to retrieve column data unique values (CAML does not have a select distinct query). It's in these unique cases that I fall back to a DVWP doing the heavy lifting. As I understand it sharepoint also does allot of smart caching on the server, so it should not increase the response time too much.

Every case is different.


Sent from mobile device.
Oct 18, 2012 at 10:36 PM

I've been able to do this with success albeit with some frustration. I was also able to do it with 2 web parts instead of 4 with creative XSLT and query string parameter filtering.  Only draw back to this approach is that it is going to force page reloads, unless I try to stuff them on a separate page and AJAX the data in.

Oct 19, 2012 at 3:41 AM
Use a hidden iframe to load the data and retrieve it from teh iframe instead of doing a full page refresh... Basically: screen scrapping. :)

Paul T