>

임시 테이블을 사용하여 Bing maps XML의 방대한 결과를 가져 와서 varchar 로 변환하는 저장 프로 시저를 만들었습니다.  여러 필드를 검색하는 데 사용할 수 있습니다. 그것을 varchar 로 변환하는 이유  XML이 sp_OAMethod 에 비해 너무 크기 때문에  내 변수는 항상 비어있었습니다.

set @serviceUrl = 'https://dev.virtualearth.net/REST/v1/Routes/Truck?wp.0=' + @ToAddress  + '&wp.1=' + @FromAddress + '&vehicleHazardousMaterials=Flammable&output=xml&key=XXX-000-XXX'
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',@serviceUrl, 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
exec @returnCode = sp_oamethod @Object, 'read', @ResponseText out, -1
Create table #tmp(dt xml)
insert into #tmp
    exec @hr = sp_OAGetProperty @Object, 'responseXML.XML'
Set @ResponseText = Convert(varchar(max), (SELECT dt from #tmp))
Drop Table #tmp 
select REPLACE(SUBSTRING(@ResponseText,PATINDEX('%<TravelDistance>%',@ResponseText),21),'<TravelDistance>','') Miles
  ,REPLACE(REPLACE(SUBSTRING(@ResponseText,PATINDEX('%<TravelDuration>%',@ResponseText),21),'<TravelDuration>',''),'<','')/60 TravelMinutes

위는 완전한 코드가 아니다;읽기 쉽도록 심각하게 수정했습니다.

할 수있는 더 좋은 방법이 있다고 확신하지만 이것이 효과가 있으며 매우 흥미 롭습니다.

그러나 계획은 다른 임시 쿼리와 함께 사용하여 동일한 데이터를 검색 할 수있는 함수를 작성하는 것이 었습니다. 임시 테이블 또는 함수 내에서 테이블을 채우기 때문에 함수로 사용할 수 없습니다.

누구나 이것을 가능하게하는 방법이 있습니까?

  • 답변 # 1

    실제로 CLR을 사용해야합니다. 올바른 저장 프로 시저에서 멀리 떨어져 있으므로 sp_OAxxx 프로 시저를 사용하지 않는 것이 가장 좋습니다. 리턴 코드를 올바르게 처리하지 않고 잘못된 HTTP 구성 요소를 사용하고 있으며 SQL Server의 XML 구문 분석기를 사용하여 결과를 구문 분석하지 않습니다. 처음에는 CLR 구현이 더 많은 것처럼 보이지만 sp_OAxxx 저장 프로 시저와 COM interop을 사용하는 코드를 유지 관리하는 것은 항상 고통스러운 일입니다.

    이 코드의 작동 방식, 코드 변경 방법 및 안전하고 신뢰할 수있는 사람을 이해하는 사람은 거의 없습니다. 코드베이스를 소유 한 다음 사람은 아마도 그 사람 중 하나가 아닐 것입니다. 프로젝트에서 이와 같은 코드를 사용하는 것은 좋지 않습니다.

    CLR을하고사용하는 경우 여전히 기술적으로는 가능하지만 기능을 사용해서는 안됩니다. 검색어 중간에 외부 액세스를 수행해서는 안됩니다.

    어쨌든 기존 접근 방식에 대한 수정 사항은 다음과 같습니다.

    declare @serviceURL varchar(max)
    declare @returnCode int
    declare @errorMessage nvarchar(max)
    declare @Object int
    declare @hr int
    declare @key varchar(2000) = 'Aoj...nFS'
    declare @FromAddress nvarchar(max) = '7000 N SH 161, Irving TX 75039'
    declare @ToAddress nvarchar(max) = '8617 Garland Rd, Dallas, TX 75218'
    set @serviceUrl = 'https://dev.virtualearth.net/REST/v1/Routes/Truck?wp.0=' + @ToAddress  + '&wp.1=' + @FromAddress + '&vehicleHazardousMaterials=Flammable&output=xml&key=' + @key
    
    exec @hr = sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
    IF @hr <> 0
    begin
        set @errorMessage = concat('sp_OACreate failed ', convert(varchar(20),cast(@hr as varbinary(4)),1));
        throw 60000, @errorMessage, 1;
    end;
    begin try
        Exec @hr = sp_OAMethod @Object, 'open', NULL, 'get',@serviceUrl, 'false'
        IF @hr <> 0
        begin
            set @errorMessage = concat('open failed ', convert(varchar(20),cast(@hr as varbinary(4)),1));
            throw 60000, @errorMessage, 1;
        end;
        Exec @hr = sp_OAMethod @Object, 'send'
        IF @hr <> 0
        begin
            set @errorMessage = concat('send failed ', convert(varchar(20),cast(@hr as varbinary(4)),1));
            throw 60000, @errorMessage, 1;
        end;
        declare @responseCode int;
        Exec @hr = sp_OAGetProperty @Object, 'status', @responseCode out
        IF @hr <> 0 or @responseCode <> 200
        begin
            set @errorMessage = concat('send failed hr:', convert(varchar(20),cast(@hr as varbinary(4)),1),' http response code: ', @responseCode);
            throw 60000, @errorMessage, 1;
        end;
        declare @tmp table(doc xml)
        insert into @tmp
        exec @hr = sp_OAGetProperty @Object, 'ResponseXML.XML'
        IF @hr <> 0
        begin
            set @errorMessage = concat('ResponseXML.XML failed ', convert(varchar(20),cast(@hr as varbinary(4)),1));
            throw 60000, @errorMessage, 1;
        end;
    
        WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/search/local/ws/rest/v1')
        select doc.value('(//TravelDistance)[1]', 'float') TravelDistance,
               doc.value('(//DistanceUnit)[1]', 'nvarchar(20)') DistanceUnit,
               doc.value('(//TravelDuration)[1]', 'float') TravelDuration,
               doc.value('(//DurationUnit)[1]', 'nvarchar(20)') DurationUnit
        from @tmp;
    end try
    begin catch
        exec @hr = sp_OADestroy  @Object ;
        throw;    
    end catch
    
    

관련 자료

  • 이전 java - 숫자 15로 나눌 수있는 간격 [0,360)에서 난수를 생성하십시오
  • 다음 angular5 - 각도 부모 구성 요소는 ng-content 자식 구성 요소를 수신합니다