<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <dict_CitiesResponse xmlns="http://ltl-ws.major-express.ru/edclients/">
      <dict_CitiesResult>
        <EDCity>
          <Code>73</Code>
          <Name>Архангельск</Name>
          <EngName>ARKHANGELSK</EngName>
          <IsShipper>true</IsShipper>
        </EDCity>
        <EDCity>
          <Code>74</Code>
          <Name>Астрахань</Name>
          <EngName>ASTRAKHAN</EngName>
          <IsShipper>true</IsShipper>
        </EDCity>
        <EDCity>
          <Code>75</Code>
          <Name>Барнаул(Алтайский край)</Name>
          <EngName>BARNAUL</EngName>
          <IsShipper>true</IsShipper>
        </EDCity>
        <EDCity>
          <Code>76</Code>
          <Name>Белгород</Name>
          <EngName>BELGOROD</EngName>
          <IsShipper>true</IsShipper>
        </EDCity>
      </dict_CitiesResult>
    </dict_CitiesResponse>
  </soap:Body>
</soap:Envelope>

 select
 b.n.value('@Code', 'int') as CodeCity
 --b.n.value('@Name', 'nvarchar(200)') as NameCity,
 --b.n.value('@EngName', 'nvarchar(200)') as EngName, 
 --b.n.value('@IsShipper', 'varchar(10)') as IsShipper
from
 [dbo].[tblMAJORtmpXML] a cross apply
 a.y.nodes('//EDCity') b(n);

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <dict_ConsigneesResponse xmlns="http://ltl-ws.major-express.ru/edclients/">
      <dict_ConsigneesResult>
        <AddressBook>
          <ID>1545349</ID>
          <Address>
            <Person>...</Person>
            <Phone>...</Phone>
            <Company>...</Company>
            <Address>...</Address>
            <PostIndex />
            <CityCode>105</CityCode>
          </Address>
        </AddressBook>
        <AddressBook>
          <ID>1135794</ID>
          <Address>
            <Person>...</Person>
            <Phone>...</Phone>
            <Company>...</Company>
            <Address>...</Address>
            <PostIndex />
            <CityCode>129</CityCode>
          </Address>
        </AddressBook>
        <AddressBook>
          <ID>1117808</ID>
          <Address>
            <Person>...</Person>
            <Phone>...</Phone>
            <Company>...</Company>
            <Address>...</Address>
            <PostIndex />
            <CityCode>129</CityCode>
          </Address>
        </AddressBook>
      </dict_ConsigneesResult>
    </dict_ConsigneesResponse>
  </soap:Body>
</soap:Envelope>

 select
 b.n.value('(*:ID/text())[1]', 'INT') AS ID,
 b.n.value('(*:Address/Person/text())[1]', 'nvarchar(200)') AS Person
from
 [dbo].[tblMAJORtmpConsigneesXML] a cross apply
 a.y.nodes('//*:AddressBook') b(n);