SQL Server
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 SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- This requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
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
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
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