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 【本文来自鸿网互联 (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 @nextclosedelimiter0 --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