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

概述sqlserver 调用qq map 服务,根据地点获取所在城市     首先需要解决的是如何在SQL SERVER中调用web service，其次是针对web service的返回值json进行解析； 1、调用web service create proc getCityByPosition( @url varchar(2048) --Your Web Service Url (inv

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

    首先需要解决的是如何在sql SERVER中调用web service，其次是针对web service的返回值Json进行解析；

1、调用web service

create proc getCityByposition(    @url varchar(2048) --Your Web Service Url (invoked))asdeclare @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；GORECONfigURE；GOsp_configure 'ole automation Procedures'， 1；GORECONfigURE；GO

2、解析Json字符串，使用自定义函数parseJsON

USE [pm_v3]GO/****** Object:  UserdefinedFunction [dbo].[parseJsON]    Script Date: 01/02/2015 18:06:05 ******/SET ANSI_NulLS ONGOSET QUOTED_IDENTIFIER ONGO                 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 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,@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,@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('%[{[[]%',@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,@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,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,@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),'object'             ELSE               IF SUBSTRING(@value,6)='@array'                 INSERT INTO @hIErarchy                  (name,ValueType)                  SELECT @name,7,'array'               ELSE                 IF SUBSTRING(@value,7)='@string' --it is a string                  INSERT INTO @hIErarchy                    (name,ValueType)                    SELECT @name,stringvalue,'string'                    FROM @strings                    WHERE string_ID=SUBSTRING(@value,5)                ELSE                   IF @value IN ('true','false')--a boolean!                     INSERT INTO @hIErarchy                      (name,ValueType)                      SELECT @name,@value,'boolean'                  ELSE                     IF @value='null' --it is a null                      INSERT INTO @hIErarchy                        (name,ValueType)                        SELECT @name,'null'                    ELSE                       IF PATINDEX('%[^0-9]%',@value)>0 --a real number                        INSERT INTO @hIErarchy                          (name,ValueType)                          SELECT @name,'real'                      ELSE --it must be an INT                        INSERT INTO @hIErarchy                          (name,'int'                       END        END      --and so lastly we put the root into the hIErarchy.      INSERT INTO @hIErarchy (name,ValueType)        SELECT '-','',@parent_ID-1,@type      --         RETURN      END  

总结

以上是内存溢出为你收集整理的sqlserver 调用qq map 服务,根据地点获取所在城市全部内容，希望文章能够帮你解决sqlserver 调用qq map 服务,根据地点获取所在城市所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。