Table Records - Fetch
Retrieve ServiceNow table records using SQL Server
- See SQLHTTP easy setup for ServiceNow
- See API Call documentation for parameter values and other information
- Create the stored procedure documented below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
CREATE PROCEDURE usp_ServiceNow_Table_Fetch( @Profile nvarchar(100), @TableName nvarchar(MAX), @Sysparm_query varchar(MAX) = NULL, @Sysparm_display_value varchar(5) = NULL, @Sysparm_fields varchar(MAX) = NULL, @Sysparm_view varchar(MAX) = NULL, @Sysparm_limit bigint = 10000, @Sysparm_offset bigint = NULL, @Sysparm_exclude_reference_link varchar(MAX) = NULL, @Sysparm_read_replica_category varchar(MAX) = NULL, @Sysparm_suppress_pagination_header bit = NULL, @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS DECLARE @Text_sysparm_suppress_pagination_header varchar(5) SET @Text_sysparm_suppress_pagination_header = CASE WHEN @Sysparm_suppress_pagination_header = 0 THEN 'false' <span style="color: #ff0000"> ELSE 'true' <span style="color: #ff0000"> END DECLARE @URL nvarchar(MAX) DECLARE @QueryString varchar(MAX) DECLARE @UserName nvarchar(4000) DECLARE @Password nvarchar(4000) DECLARE @HTTPSessionID uniqueidentifier SET @URL = SQLHTTP.net.AuthParam(@Profile, 'Domain') EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'api', 'now', 'table', @TableName EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'sysparm_query', @Sysparm_query, 'sysparm_display_value', @Sysparm_display_value, 'sysparm_fields', @Sysparm_fields, 'sysparm_view', @Sysparm_view, 'sysparm_limit', @Sysparm_limit, 'sysparm_offset', @Sysparm_offset, 'sysparm_exclude_reference_link', @Sysparm_exclude_reference_link, 'sysparm_read_replica_category', @Sysparm_read_replica_category, 'sysparm_suppress_pagination_header', @Text_sysparm_suppress_pagination_header SET @URL = @URL + @QueryString EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT SET @UserName = SQLHTTP.net.AuthParam(@Profile, 'UserName') SET @Password = SQLHTTP.net.AuthParam(@Profile, 'Password') EXEC SQLHTTP.net.BasicAuthHeader @HttpSessionID, @UserName, @Password EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'GET', @ContentType = 'application/json', @Accept = 'application/xml', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT GO |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) EXEC usp_ServiceNow_Table_Fetch @Profile = 'My ServiceNow', @TableName = 'alm_asset', @Sysparm_query = 'active=true^ORDERBYnumber^ORDERBYDESCcategory', @Sysparm_limit = 10, @Response = @Response OUTPUT, @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN DECLARE @X xml SET @X = CONVERT(xml, CONVERT(varchar(MAX), @Response)) --The XPath syntax below was easily generated by executing the following commands: --EXEC SQLHTTP.net.XQueryHelper @X --and then executing: --EXEC SQLHTTP.net.XQueryHelper @X, 'response/result' -- SELECT T.C.value(N'display_name[1]', N'nvarchar(MAX)') AS [display_name] ,T.C.value(N'cost[1]', N'nvarchar(MAX)') AS [cost] ,T.C.value(N'quantity[1]', N'nvarchar(MAX)') AS [quantity] FROM @X.nodes(N'/response/result') T(C) END |
1 2 3 4 5 6 7 8 |
display_name cost quantity ------------------------------------------------- ----------- ----------- P1000479 - Apple MacBook Pro 15" 1799.99 1 Samsung SyncMaster 24" Class BackLight LED 457.76 2 Logitech Logitech Desktop Optical Wireless Mouse 15 1 Logitech Logitech Desktop Keyboard 19.99 1 |
IMPORTANT DISCLAIMER
CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.
USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.
USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.