Accounts - Fetch

Retrieve detailed balance and holding information for each Ally-Invest (formerly TradeKing) account associated with a user, using SQL Server



Prerequisites:

See SQLHTTP easy setup for Ally Invest (formerly TradeKing)
See API Call documentation for parameter values and other information
Create the stored procedure documented below



Stored Procedure:


CREATE PROCEDURE usp_AllyInvest_Accounts_Fetch(
		@Response nvarchar(MAX) OUTPUT,
		@StatusCode int OUTPUT,
		@StatusDescription nvarchar(MAX) OUTPUT)
AS
 
DECLARE @URL nvarchar(MAX)
DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://api.tradeking.com/v1/accounts.xml'

EXEC SQLHTTP.net.URLBuilder   @URL OUTPUT,
			      'TradeKing'

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC usp_AllyInvest_Auth_Header @HTTPSessionID, @URL, 'GET'

EXEC SQLHTTP.net.HTTPRequest	@HttpSessionID, 
				@URL = @URL,
				@Method = 'GET',
				@StatusCode = @StatusCode OUTPUT,
				@StatusDescription = @StatusDescription OUTPUT,
				@Response = @Response OUTPUT
GO

CREATE PROCEDURE usp_AllyInvest_Accounts_Fetch(

@Response nvarchar(MAX) OUTPUT,

@StatusCode int OUTPUT,

@StatusDescription nvarchar(MAX) OUTPUT)

DECLARE @URL nvarchar(MAX)

DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://api.tradeking.com/v1/accounts.xml'

EXEC SQLHTTP.net.URLBuilder @URL OUTPUT,

'TradeKing'

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC usp_AllyInvest_Auth_Header @HTTPSessionID, @URL, 'GET'

EXEC SQLHTTP.net.HTTPRequest @HttpSessionID,

@URL = @URL,

@Method = 'GET',

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT,

@Response = @Response OUTPUT



Sample Usage:


DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_AllyInvest_Accounts_Fetch
			@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))
		
		DECLARE @ResponseError varchar(MAX)
		SELECT @ResponseError = T.C.value(N'error[1]', N'varchar(MAX)') 
		FROM @X.nodes(N'/response') T(C)

		--Use the SQLHTTP.net.XQueryHelper stored procedure to retrieve additional XPath syntax for your XML
		IF @ResponseError = 'Success'
			BEGIN
				SELECT   T.C.value(N'accountsummary[1]/account[1]', N'nvarchar(MAX)') AS [account]
					,T.C.value(N'accountsummary[1]/accountbalance[1]/accountvalue[1]', N'nvarchar(MAX)') AS [accountvalue]
					,T.C.value(N'accountsummary[1]/accountbalance[1]/money[1]/cashavailable[1]', N'nvarchar(MAX)') AS [cashavailable]
					,T.C.value(N'accountsummary[1]/accountbalance[1]/money[1]/marginbalance[1]', N'nvarchar(MAX)') AS [marginbalance]
				FROM @X.nodes(N'/response/accounts') T(C)
			END
		ELSE
			BEGIN
				RAISERROR(@ResponseError, 16, 1)
			END
	END

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_AllyInvest_Accounts_Fetch

@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))

DECLARE @ResponseError varchar(MAX)

SELECT @ResponseError = T.C.value(N'error[1]', N'varchar(MAX)')

FROM @X.nodes(N'/response') T(C)

--Use the SQLHTTP.net.XQueryHelper stored procedure to retrieve additional XPath syntax for your XML

IF @ResponseError = 'Success'

BEGIN

SELECT T.C.value(N'accountsummary[1]/account[1]', N'nvarchar(MAX)') AS [account]

,T.C.value(N'accountsummary[1]/accountbalance[1]/accountvalue[1]', N'nvarchar(MAX)') AS [accountvalue]

,T.C.value(N'accountsummary[1]/accountbalance[1]/money[1]/cashavailable[1]', N'nvarchar(MAX)') AS [cashavailable]

,T.C.value(N'accountsummary[1]/accountbalance[1]/money[1]/marginbalance[1]', N'nvarchar(MAX)') AS [marginbalance]

FROM @X.nodes(N'/response/accounts') T(C)

END

ELSE

BEGIN

RAISERROR(@ResponseError, 16, 1)

END

account accountvalue cashavailable marginbalance

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

3......7 0 0 0



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.