(SQL Server) SharePoint -- Get File Property

See more SharePoint Examples

Demonstrates how to get a specific property of a SharePoint file. Any of the following properties can be retrieved:

• Author: Gets a value that specifies the user who added the file.
• CheckedOutByUser: Gets a value that returns the user who has checked out the file.
• CheckInComment: Gets a value that returns the comment used when a document is checked in to a document library.
• CheckOutType: Gets a value that indicates how the file is checked out of a document library. Represents an
• ContentTag: Returns internal version of content, used to validate document equality for read purposes.
• CustomizedPageStatus: Gets a value that specifies the customization status of the file. Represents an
• ETag: Gets a value that specifies the ETag value.
• Exists: Gets a value that specifies whether the file exists.
• Length: Gets the size of the file in bytes, excluding the size of any Web Parts that are used in the file.
• Level: Gets a value that specifies the publishing level of the file. Represents an
• ListItemAllFields: Gets a value that specifies the list item field values for the list item corresponding to the file.
• LockedByUser: Gets a value that returns the user that owns the current lock on the file.
• MajorVersion: Gets a value that specifies the major version of the file.
• MinorVersion: Gets a value that specifies the minor version of the file.
• ModifiedBy: Gets a value that returns the user who last modified the file.
• Name: Gets the name of the file including the extension.
• ServerRelativeUrl: Gets the relative URL of the file based on the URL for the server.
• TimeCreated: Gets a value that specifies when the file was created.
• TimeLastModified: Gets a value that specifies when the file was last modified.
• Title: Gets a value that specifies the display name of the file.
• UiVersion: Gets a value that specifies the implementation-specific version identifier of the file.
• UiVersionLabel: Gets a value that specifies the implementation-specific version identifier of the file.
• Versions: Gets a value that returns a collection of file version objects that represent the versions of the file.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- If SharePoint Windows classic authentication is used, then set the 
    -- Login, Password, LoginDomain, and NtlmAuth properties.
    EXEC sp_OASetProperty @http, 'Login', 'SHAREPOINT_USERNAME'
    EXEC sp_OASetProperty @http, 'Password', 'SHAREPOINT_PASSWORD'
    EXEC sp_OASetProperty @http, 'LoginDomain', 'SHAREPOINT_NTLM_DOMAIN'
    EXEC sp_OASetProperty @http, 'NtlmAuth', 1

    -- The more common case is to use SharePoint Online authentication (via the SPOIDCRL cookie).
    -- If so, do not set Login, Password, LoginDomain, and NtlmAuth, and instead
    -- establish the cookie as shown at SharePoint Online Authentication

    -- Indicate that we want a JSON reply
    EXEC sp_OASetProperty @http, 'Accept', 'application/json;odata=verbose'
    EXEC sp_OASetProperty @http, 'AcceptCharset', 'utf-8'

    -- Get the Author property.
    DECLARE @url nvarchar(4000)
    SELECT @url = 'https://SHAREPOINT_HTTPS_DOMAIN/_api/web/GetFileByServerRelativeUrl(''/Documents/VCAC-document.docx'')/Author'

    DECLARE @jsonReply nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @jsonReply OUT, @url
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    DECLARE @success int
    EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonReply
    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    -- Make sure it was a success response, and that we really have metadata.
    -- If it was an error response, then the JSON is error information..
    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- The Author property returns JSON looking like this:

    -- {
    --   "d": {
    --     "__metadata": {
    --       "id": "Web/GetUserById(18)",
    --       "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/Web/GetUserById(18)",
    --       "type": "SP.User"
    --     },
    --     "Groups": {
    --       "__deferred": {
    --         "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/Web/GetUserById(18)/Groups"
    --       }
    --     },
    --     "Id": 18,
    --     "IsHiddenInUI": false,
    --     "LoginName": "i:0#.w|mydomain\\msmith",
    --     "Title": "Mike Smith",
    --     "PrincipalType": 1,
    --     "Email": "msmith@mydomain.com",
    --     "IsSiteAdmin": false,
    --     "UserId": {
    --       "__metadata": {
    --         "type": "SP.UserIdInfo"
    --       },
    --       "NameId": "s-1-5-21-3433503314-2897774614-343593928-1137",
    --       "NameIdIssuer": "urn:office:idp:activedirectory"
    --     }
    --   }
    -- }
    -- 

    -- Get the Title and Email:

    PRINT '----'

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'd.Title'
    PRINT 'Title: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'd.Email'
    PRINT 'Email: ' + @sTmp0

    PRINT '----'

    -- --------------------------------------------------------------------

    -- Get the Versions property.
    SELECT @url = 'https://SHAREPOINT_HTTPS_DOMAIN/_api/web/GetFileByServerRelativeUrl(''/Documents/VCAC-document.docx'')/Versions'

    EXEC sp_OAMethod @http, 'QuickGetStr', @jsonReply OUT, @url
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonReply
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- The Versions JSON reply looks like this:

    -- {
    --   "d": {
    --     "results": [
    --       {
    --         "__metadata": {
    --           "id": "a8d025e2-8255-4487-9edb-9e796ab2889c",
    --           "type": "SP.FileVersion"
    --         },
    --         "CreatedBy": {
    --           "__deferred": {
    --             "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/CreatedBy"
    --           }
    --         },
    --         "CheckInComment": "",
    --         "Created": "2016-03-15T02:22:26Z",
    --         "ID": 1,
    --         "IsCurrentVersion": false,
    --         "Size": 21082,
    --         "Url": "_vti_history/1/Documents/VCAC-document.docx",
    --         "VersionLabel": "0.1"
    --       },
    --       {
    --         "__metadata": {
    --           "id": "8ab3eadd-9126-4f65-a2a3-3b0689c592d6",
    --           "type": "SP.FileVersion"
    --         },
    --         "CreatedBy": {
    --           "__deferred": {
    --             "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/CreatedBy"
    --           }
    --         },
    --         "CheckInComment": "",
    --         "Created": "2016-03-15T05:28:24Z",
    --         "ID": 2,
    --         "IsCurrentVersion": false,
    --         "Size": 21082,
    --         "Url": "_vti_history/2/Documents/VCAC-document.docx",
    --         "VersionLabel": "0.2"
    --       }
    --     ]
    --   }
    -- }
    -- 

    -- Get each VersionLabel, Size, and Url...
    DECLARE @numVersions int
    EXEC sp_OAMethod @json, 'SizeOfArray', @numVersions OUT, 'd.results'
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numVersions
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i

        PRINT '---- i = ' + @i

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'd.results[i].VersionLabel'
        PRINT 'VersionLabel: ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'd.results[i].Url'
        PRINT 'Url: ' + @sTmp0

        EXEC sp_OAMethod @json, 'IntOf', @iTmp0 OUT, 'd.results[i].Size'
        PRINT 'Size: ' + @iTmp0
        SELECT @i = @i + 1
      END

    -- The output is:

    -- ---- i = 0
    -- VersionLabel: 0.1
    -- Url: _vti_history/1/Documents/VCAC-document.docx
    -- Size: 21082
    -- ---- i = 1
    -- VersionLabel: 0.2
    -- Url: _vti_history/2/Documents/VCAC-document.docx
    -- Size: 21082

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json


END
GO