select @metod = 'documentElement.childNodes.item(' + cast(@i as varchar(10)) + ').attributes.item("regionCode").text'

 set nocount on
 
declare
	@i        int,
	@count    int,
	@item     varchar(8000),
	@metod    varchar(128),
	@obj      int,
	@ret_val  int,
	@return   int,
	@text     varchar(8000),
	@url         varchar(512),

	@RegionCode as varchar(10),
	@RegionName as varchar(100),
	@FildName as varchar(50)  

declare @Regions table (
	regionName varchar(100),
	RegionCode varchar(10)  
   )

select @url ='https://integration.cdek.ru/v1/location/regions?'

select @i = 0
 
exec @return = sp_OACreate 'MSXML2.DOMDocument', @obj output
exec @return = sp_OASetProperty @obj, 'async', 0
exec @return = sp_OAMethod @obj, 'load', @ret_val output, @url
select @metod = 'documentElement.childNodes.length'
exec @return = sp_OAMethod @obj, @metod, @item output 
select @count = cast(@item as int) 

while @i < @count
begin
		--RegionName
		select @metod = 'documentElement.childNodes.item(' + cast(@i as varchar(10)) + ').attributes.item(0).text'	
		exec @return = sp_OAMethod @obj, @metod, @item output 
		select @RegionName = @item

		---RegionCode
		select @metod = 'documentElement.childNodes.item(' + cast(@i as varchar(10)) + ').attributes.item(1).name'	
		--!!!! select @metod = 'documentElement.childNodes.item(' + cast(@i as varchar(10)) + ').attributes.item("regionCode").text'	
		exec @return = sp_OAMethod @obj, @metod, @item output 
		select @FildName = @item
		
		if @FildName='regionCode' 
		begin
			select @metod = 'documentElement.childNodes.item(' + cast(@i as varchar(10)) + ').attributes.item(1).text'	
			exec @return = sp_OAMethod @obj, @metod, @item output 
			select @RegionCode = @item
		end
		else		select @RegionCode = ''


		insert into @Regions (RegionName, RegionCode) 
		select @RegionName, @RegionCode

		select @i = @i + 1
end

exec @return = sp_OADestroy @obj

select * from @Regions 

select @metod = 'documentElement.childNodes.item(' + cast(@i as varchar(10)) + ').getAttribute("regionCode").value'

declare @t table (x xml);

declare @ret_val int, @result int, @obj int, @vResponseText varchar(8000), @url varchar(100) = 'https://integration.cdek.ru/pvzlist/v1/xml';

exec @result = sp_OACreate 'MSXML2.DOMDocument', @obj output;
if @result <> 0
 exec sys.sp_OAGetErrorInfo @obj;

exec @result = sp_OASetProperty @obj, 'async', 0;
if @result <> 0
 exec sys.sp_OAGetErrorInfo @obj;

exec @result = sp_OAMethod @obj, 'load', @ret_val output, @url;
if @result <> 0
 exec sys.sp_OAGetErrorInfo @obj;

insert into @t
 exec @result = sp_OAGetProperty @obj, 'xml';
if @result <> 0
 exec sys.sp_OAGetErrorInfo @obj;

select
 b.n.value('@Code', 'nvarchar(10)') as Code,
 b.n.value('@PostalCode', 'nvarchar(10)') as PostalCode,
 b.n.value('@Name', 'nvarchar(100)') as Name,
 b.n.value('@CountryCode', 'nvarchar(10)') as CountryCode,
 b.n.value('@countryCodeIso', 'nvarchar(10)') as countryCodeIso,
 b.n.value('@CountryName', 'nvarchar(100)') as CountryName,
 b.n.value('@RegionCode', 'nvarchar(10)') as RegionCode,
 b.n.value('@RegionName', 'nvarchar(100)') as RegionName,
 b.n.value('@CityCode', 'nvarchar(10)') as CityCode,
 b.n.value('@City', 'nvarchar(100)') as City,
 b.n.value('@WorkTime', 'nvarchar(100)') as WorkTime,
 b.n.value('@Address', 'nvarchar(200)') as Address,
 b.n.value('@FullAddress', 'nvarchar(200)') as FullAddress,
 b.n.value('@AddressComment', 'nvarchar(300)') as AddressComment,
 b.n.value('@Phone', 'nvarchar(100)') as Phone,
 b.n.value('@Email', 'nvarchar(100)') as Email,
 b.n.value('@qqId', 'nvarchar(10)') as qqId,
 b.n.value('@Note', 'nvarchar(100)') as Note,
 b.n.value('@coordX', 'nvarchar(20)') as coordX,
 b.n.value('@coordY', 'nvarchar(20)') as coordY,
 b.n.value('@Type', 'nvarchar(10)') as Type,
 b.n.value('@ownerCode', 'nvarchar(10)') as ownerCode,
 b.n.value('@IsDressingRoom', 'nvarchar(10)') as IsDressingRoom,
 b.n.value('@HaveCashless', 'nvarchar(10)') as HaveCashless,
 b.n.value('@AllowedCod', 'nvarchar(10)') as AllowedCod,
 b.n.value('@NearestStation', 'nvarchar(200)') as NearestStation,
 b.n.value('@MetroStation', 'nvarchar(100)') as MetroStation,
 b.n.value('@Site', 'nvarchar(100)') as Site 
  
  
from
 @t a cross apply
 a.x.nodes('/PvzList/Pvz') b(n)

exec sys.sp_OADestroy @obj;

 select
 b.n.value('@number', 'nvarchar(100)') as number 

from
 @t a cross apply
 a.x.nodes('/PvzList/Pvz/PhoneDetail') b(n)

b.n.query('data(PhoneDetail/@number)').value('.', 'nvarchar(1000)')

select @metod = 'documentElement.childNodes.item(' + cast(@i as varchar(10)) + ').attributes.getNamedItem("regionCode").Text'