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

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

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