CAML query with Person or People and Groups column

Today I want to highlight CAML query with Person or People and Groups column. When we retrieve that column, we will get the result as {UserID};#{UserName} format, e.g.: 96;#Lee, Kelvin.

If you attempt to query base on the column using exactly the same format, it won’t works. Look at the following example how to query this column type.

This doesn’t works.

<Eq><FieldRef Name='UserID' /><Value Type='User'>96;#Lee, Kelvin</Value></Eq>

This will works. Only User Name required.

<Eq><FieldRef Name='UserID' /><Value Type='User'>Lee, Kelvin</Value></Eq>

This works too. Only User ID required. Must specify LookupId= ‘TRUE’

<Eq><FieldRef Name='UserID' LookupId= 'TRUE'/><Value Type='User'>96</Value></Eq>

SSIS and SharePoint Web Service – GetListItems

Recently working on SSIS to get SharePoint List Item. Using SSIS Script Component, we can call the SharePoint web service. Proxy class can be generated using Web Services Description Language Tool (Wsdl.exe)

Dim ListsService As Lists = New Lists() 'Web Service proxy class
ListsService.Url = "http://kelvinsharepointsite/_vti_bin/lists.asmx"
ListsService.Credentials = System.Net.CredentialCache.DefaultCredentials

'Create Query CAML
Dim xmlQueryDoc As New XmlDocument
Dim ndQuery As XmlNode = xmlQueryDoc.CreateNode(XmlNodeType.Element, "Query", "") 'Create <Query> node
Dim ndViewFields As XmlNode = xmlQueryDoc.CreateNode(XmlNodeType.Element, "ViewFields", "") 'Create <ViewFields> node
Dim ndQueryOptions As XmlNode = xmlQueryDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "") 'Create <QueryOptions> node

ndQuery.InnerXml = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>My Title Here</Value></Eq></Where>"
ndViewFields.InnerXml = "<FieldRef Name='Description'/>"
ndQueryOptions.InnerXml = ""
Dim listName As String = "My List Name Here"
Dim rowLimit As String = "1"

'Lists.GetListItems (listName, viewName, query, viewFields, rowLimit, queryOptions, webID)
Dim ndListItems As XmlNode = ListsService.GetListItems(listName, Nothing, ndQuery, ndViewFields, rowLimit, ndQueryOptions, Nothing)

Dim xmlResultDoc As New XmlDocument()
'For debug

Dim nsmgr As New XmlNamespaceManager(xmlResultDoc.NameTable)
nsmgr.AddNamespace("z", "#RowsetSchema")

Dim rows As XmlNodeList = ndListItems.SelectNodes("//z:row", nsmgr)
Dim value As String = rows(0).Attributes("ows_Description").Value
MsgBox("Returned value: " + value)

'For iteration multiple rows
'For Each xNode As XmlNode In rows
'    MsgBox(xNode.Attributes("ows_Description").Value)