Home >Database >Mysql Tutorial >sqlserver调用qqmap服务,根据地点获取所在城市
sqlserver调用qqmap服务,根据地点获取所在城市
- WBOYOriginal
- 2016-06-07 16:13:121128browse
sqlserver 调用qq map 服务,根据地点获取所在城市 首先需要解决的是如何在SQL SERVER中调用web service，其次是针对web service的返回json进行解析； 1、调用web service create proc getCityByPosition( @url varchar(2048) --Your Web Service Url (invoke
sqlserver 调用qq map 服务,根据地点获取所在城市
首先需要解决的是如何在SQL SERVER中调用web service，其次是针对web service的返回值json进行解析；
1、调用web service
create proc getCityByPosition(
@url varchar(2048) --Your Web Service Url (invoked)
)
as
declare @Object as Int;
declare @ResponseText as Varchar(8000);
declare @resultStr varchar(2048)
-- exec getCityByPosition 'http://apis.map.qq.com/ws/geocoder/v1/?location=34.287100,117.255000&key=K76BZ-W3O2Q-RFL5S-GXOPR-3ARIT-6KFE5&output=json&&callback=?'
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',@url,'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Select top 1 StringValue
from parseJSON(@ResponseText) where name = 'city';
Exec sp_OADestroy @Object执行测试可能报错，提示更改全局配置，需管理员执行下面代码：
sp_configure 'show advanced options'， 1； GO RECONFIGURE； GO sp_configure 'Ole Automation Procedures'， 1； GO RECONFIGURE； GO
2、解析json字符串，使用自定义函数parseJSON
USE [pm_v3]
GO
/****** Object: UserDefinedFunction [dbo].[parseJSON] Script Date: 01/02/2015 18:06:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[parseJSON]( @JSON NVARCHAR(MAX))
RETURNS @hierarchy TABLE
(
element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
NAME VARCHAR(2000),/* the name of the object */
StringValue VARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */
ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/
)
AS
BEGIN
DECLARE
@FirstObject INT, --the index of the first open bracket found in the JSON string
@OpenDelimiter INT,--the index of the next open bracket found in the JSON string
@NextOpenDelimiter INT,--the index of subsequent open bracket found in the JSON string
【本文来自鸿网互联 (http://www.68idc.cn)】 @NextCloseDelimiter INT,--the index of subsequent close bracket found in the JSON string
@Type NVARCHAR(10),--whether it denotes an object or an array
@NextCloseDelimiterChar CHAR(1),--either a '}' or a ']'
@Contents NVARCHAR(MAX), --the unparsed contents of the bracketed expression
@Start INT, --index of the start of the token that you are parsing
@end INT,--index of the end of the token that you are parsing
@param INT,--the parameter at the end of the next Object/Array token
@EndOfName INT,--the index of the start of the parameter at end of Object/Array token
@token NVARCHAR(200),--either a string or object
@value NVARCHAR(MAX), -- the value as a string
@name NVARCHAR(200), --the name as a string
@parent_ID INT,--the next parent ID to allocate
@lenJSON INT,--the current length of the JSON String
@characters NCHAR(36),--used to convert hex to decimal
@result BIGINT,--the value of the hex symbol being parsed
@index SMALLINT,--used for parsing the hex value
@Escape INT --the index of the next escape character
DECLARE @Strings TABLE /* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped' in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in the JSON string by tokens representing the string */
(
String_ID INT IDENTITY(1, 1),
StringValue NVARCHAR(MAX)
)
SELECT--initialise the characters to convert hex to ascii
@characters='0123456789abcdefghijklmnopqrstuvwxyz',
/* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */
@parent_ID=0;
WHILE 1=1 --forever until there is nothing more to do
BEGIN
SELECT
@start=PATINDEX('%[^a-zA-Z]["]%', @json);--next delimited string
IF @start=0 BREAK --no more so drop through the WHILE loop
IF SUBSTRING(@json, @start+1, 1)='"'
BEGIN --Delimited Name
SET @start=@Start+1;
SET @end=PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start));
END
IF @end=0 --no end delimiter to last string
BREAK --no more
SELECT @token=SUBSTRING(@json, @start+1, @end-1)
--now put in the escaped control characters
SELECT @token=REPLACE(@token, FROMString, TOString)
FROM
(SELECT
'\"' AS FromString, '"' AS ToString
UNION ALL SELECT '\\', '\'
UNION ALL SELECT '\/', '/'
UNION ALL SELECT '\b', CHAR(08)
UNION ALL SELECT '\f', CHAR(12)
UNION ALL SELECT '\n', CHAR(10)
UNION ALL SELECT '\r', CHAR(13)
UNION ALL SELECT '\t', CHAR(09)
) substitutions
SELECT @result=0, @escape=1
--Begin to take out any hex escape codes
WHILE @escape>0
BEGIN
SELECT @index=0,
--find the next hex escape sequence
@escape=PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token)
IF @escape>0 --if there is one
BEGIN
WHILE @index<4 --there are always four digits to a \x sequence
BEGIN
SELECT --determine its value
@result=@result+POWER(16, @index)
*(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1),
@characters)-1), @index=@index+1 ;
END
-- and replace the hex sequence by its unicode value
SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result))
END
END
--now store the string away
INSERT INTO @Strings (StringValue) SELECT @token
-- and replace the string with a token
SELECT @JSON=STUFF(@json, @start, @end+1,
'@string'+CONVERT(NVARCHAR(5), @@identity))
END
-- all strings are now removed. Now we find the first leaf.
WHILE 1=1 --forever until there is nothing more to do
BEGIN
SELECT @parent_ID=@parent_ID+1
--find the first object or list by looking for the open bracket
SELECT @FirstObject=PATINDEX('%[{[[]%', @json)--object or array
IF @FirstObject = 0 BREAK
IF (SUBSTRING(@json, @FirstObject, 1)='{')
SELECT @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@firstObject
WHILE 1=1 --find the innermost object or list...
BEGIN
SELECT
@lenJSON=LEN(@JSON+'|')-1
--find the matching close-delimiter proceeding after the open-delimiter
SELECT
@NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json,
@OpenDelimiter+1)
--is there an intervening open-delimiter of either type?
SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%',
RIGHT(@json, @lenJSON-@OpenDelimiter))--object
IF @NextOpenDelimiter=0 --then we are done.
BREAK
SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter
IF @NextCloseDelimiter<@NextOpenDelimiter --we have found the next leaf
BREAK
--we prepare to walk the document further
IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{'
SELECT @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@NextOpenDelimiter
END
/*and now we can parse out the list or name/value pairs. We first pull out the structure into the variable '@contents' and replace it in the JSON document with a token representing it.*/
SELECT
@contents=SUBSTRING(@json, @OpenDelimiter+1,
@NextCloseDelimiter-@OpenDelimiter-1)
SELECT
@JSON=STUFF(@json, @OpenDelimiter,
@NextCloseDelimiter-@OpenDelimiter+1,
'@'+@type+CONVERT(NVARCHAR(5), @parent_ID))
/*and do each name/value pair, or just value, in the case of an ordered value list. */
WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents))<>0
BEGIN
IF @Type='Object' /*it will be a 0-n list containing a string followed by a string, number,boolean, or null*/
BEGIN
SELECT
@end=CHARINDEX(':', ' '+@contents) /*if there is anything, it will be a string-based name.*/
SELECT @start=PATINDEX('%[^A-Za-z@][@]%', ' '+@contents)--find out what the token is
SELECT @token=SUBSTRING(' '+@contents, @start+1, @End-@Start-1),
@endofname=PATINDEX('%[0-9]%', @token),--and find out the number so as to fish out the string
@param=RIGHT(@token, LEN(@token)-@endofname+1)
SELECT--separate the token from the contents of the structure (chop, chop0
@token=LEFT(@token, @endofname-1),
@Contents=RIGHT(' '+@contents, LEN(' '+@contents+'|')-@end-1)
--now we get the string we have stored (names are stored as strings)
SELECT @name=stringvalue FROM @strings
WHERE string_id=@param --fetch the name
END
ELSE --it is merely a value in an ordered list, without a name
SELECT @Name=null
SELECT
@end=CHARINDEX(',', @contents)-- a string-token, object-token, list-token, number,boolean, or null
IF @end=0 --then we're at the end of the list
SELECT @end=PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents+' ')
+1
SELECT @start=PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' '+@contents)
SELECT --get the value and snip the contents
@Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)),
@Contents=RIGHT(@contents+' ', LEN(@contents+'|')-@end)
IF SUBSTRING(@value, 1, 7)='@object'
INSERT INTO @hierarchy
(NAME, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @parent_ID, SUBSTRING(@value, 8, 5),
SUBSTRING(@value, 8, 5), 'object'
ELSE
IF SUBSTRING(@value, 1, 6)='@array'
INSERT INTO @hierarchy
(NAME, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @parent_ID, SUBSTRING(@value, 7, 5),
SUBSTRING(@value, 7, 5), 'array'
ELSE
IF SUBSTRING(@value, 1, 7)='@string' --it is a string
INSERT INTO @hierarchy
(NAME, parent_ID, StringValue, ValueType)
SELECT @name, @parent_ID, stringvalue, 'string'
FROM @strings
WHERE string_id=SUBSTRING(@value, 8, 5)
ELSE
IF @value IN ('true', 'false')--a boolean!
INSERT INTO @hierarchy
(NAME, parent_ID, StringValue, ValueType)
SELECT @name, @parent_ID, @value, 'boolean'
ELSE
IF @value='null' --it is a null
INSERT INTO @hierarchy
(NAME, parent_ID, StringValue, ValueType)
SELECT @name, @parent_ID, @value, 'null'
ELSE
IF PATINDEX('%[^0-9]%', @value)>0 --a real number
INSERT INTO @hierarchy
(NAME, parent_ID, StringValue, ValueType)
SELECT @name, @parent_ID, @value, 'real'
ELSE --it must be an INT
INSERT INTO @hierarchy
(NAME, parent_ID, StringValue, ValueType)
SELECT @name, @parent_ID, @value, 'int'
END
END
--and so lastly we put the root into the hierarchy.
INSERT INTO @hierarchy (NAME, parent_ID, StringValue, Object_ID, ValueType)
SELECT '-', NULL, '', @parent_id-1, @type
--
RETURN
END
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：rhel5下禁用SElinux的方法Next article：3年前oracle命令行笔记，到现在还常用。