sqlserver调用qqmap服务,根据地点获取所在城市

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

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