ALTER proc [dbo].[usp_xml_dadata_get_shortlist_from_orgs](@doc varchar(max))
as 
BEGIN
--exec [dbo].[usp_xml_dadata_get_shortlist_from_orgs] '<?xml version="1.0" encoding="windows-1251"?><SuggestResponse><suggestions><value>ПАО СБЕРБАНК</value><unrestricted_value>ПАО СБЕРБАНК</unrestricted_value><data><kpp>775001001</kpp><management><name>Греф Герман Оскарович</name><post>Президент-председатель правления</post></management><branch_type>MAIN</branch_type><branch_count>91</branch_count><type>LEGAL</type><opf><code>12247</code><full>Публичное акционерное общество</full><short>ПАО</short></opf><name><full_with_opf>ПУБЛИЧНОЕ АКЦИОНЕРНОЕ ОБЩЕСТВО "СБЕРБАНК РОССИИ"</full_with_opf><short_with_opf>ПАО СБЕРБАНК</short_with_opf><latin/><full>СБЕРБАНК РОССИИ</full><short>СБЕРБАНК</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved>64.19</okved><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date>677376000000</registration_date><liquidation_date/></state><address><value>г Москва, ул Вавилова, д 19</value><unrestricted_value>г Москва, ул Вавилова, д 19</unrestricted_value><data><postal_code>117312</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>г Москва</region_with_type><region_type>г</region_type><region_type_full>город</region_type_full><region>Москва</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Москва</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Москва</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>ул Вавилова</street_with_type><street_type>ул</street_type><street_type_full>улица</street_type_full><street>Вавилова</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>19</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>93409d8c-d8d4-4491-838f-f9aa1678b5e6</fias_id><fias_level/><kladr_id>7700000000009240170</kladr_id><capital_marker/><okato>45293554000</okato><oktmo>45397000</oktmo><tax_office>7736</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г АБАКАН)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г АБАКАН)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г АБАКАН)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г АБАКАН)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г АБАКАН)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г АБАКАН)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>655017 РЕСПУБЛИКА ХАКАСИЯ ГОРОД АБАКАН УЛИЦА ПУШКИНА ДОМ 165 ЛИТЕР А ПОМЕЩЕНИЯ 2Н, 3Н</value><unrestricted_value>655017 РЕСПУБЛИКА ХАКАСИЯ ГОРОД АБАКАН УЛИЦА ПУШКИНА ДОМ 165 ЛИТЕР А ПОМЕЩЕНИЯ 2Н, 3Н</unrestricted_value><data/></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г АРХАНГЕЛЬСК)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г АРХАНГЕЛЬСК)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г АРХАНГЕЛЬСК)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г АРХАНГЕЛЬСК)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г АРХАНГЕЛЬСК)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г АРХАНГЕЛЬСК)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Архангельская обл, г Архангельск, пр-кт Ломоносова, д 137</value><unrestricted_value>Архангельская обл, г Архангельск, пр-кт Ломоносова, д 137</unrestricted_value><data><postal_code>163000</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Архангельская обл</region_with_type><region_type>обл</region_type><region_type_full>область</region_type_full><region>Архангельская</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Архангельск</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Архангельск</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>пр-кт Ломоносова</street_with_type><street_type>пр-кт</street_type><street_type_full>проспект</street_type_full><street>Ломоносова</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>137</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>f7eb063b-0095-47a1-b0d3-213656bcc07b</fias_id><fias_level/><kladr_id>2900000100002580159</kladr_id><capital_marker/><okato>11401000000</okato><oktmo>11701000</oktmo><tax_office>2901</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г АСТРАХАНЬ)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г АСТРАХАНЬ)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г АСТРАХАНЬ)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г АСТРАХАНЬ)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г АСТРАХАНЬ)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г АСТРАХАНЬ)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Астраханская обл, г Астрахань, ул Кирова, д 41</value><unrestricted_value>Астраханская обл, г Астрахань, ул Кирова, д 41</unrestricted_value><data><postal_code>414000</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Астраханская обл</region_with_type><region_type>обл</region_type><region_type_full>область</region_type_full><region>Астраханская</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Астрахань</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Астрахань</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>ул Кирова</street_with_type><street_type>ул</street_type><street_type_full>улица</street_type_full><street>Кирова</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>41</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>f76cf1a7-4494-4bc1-b696-e2569361550a</fias_id><fias_level/><kladr_id>3000000100040950047</kladr_id><capital_marker/><okato>12401000000</okato><oktmo>12701000</oktmo><tax_office>3015</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г БАРНАУЛ)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г БАРНАУЛ)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БАРНАУЛ)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БАРНАУЛ)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г БАРНАУЛ)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г БАРНАУЛ)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Алтайский край, г Барнаул, Комсомольский пр-кт, д 106А</value><unrestricted_value>Алтайский край, г Барнаул, Комсомольский пр-кт, д 106А</unrestricted_value><data><postal_code>656038</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Алтайский край</region_with_type><region_type>край</region_type><region_type_full>край</region_type_full><region>Алтайский</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Барнаул</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Барнаул</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>Комсомольский пр-кт</street_with_type><street_type>пр-кт</street_type><street_type_full>проспект</street_type_full><street>Комсомольский</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>106А</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>7a60a1df-0938-414d-8e6e-a141b9c48e97</fias_id><fias_level/><kladr_id>2200000100003180065</kladr_id><capital_marker/><okato>01401367000</okato><oktmo>01701000</oktmo><tax_office>2224</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г БЕЛГОРОД)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г БЕЛГОРОД)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БЕЛГОРОД)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БЕЛГОРОД)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г БЕЛГОРОД)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г БЕЛГОРОД)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Белгородская обл, г Белгород, Гражданский пр-кт, д 52</value><unrestricted_value>Белгородская обл, г Белгород, Гражданский пр-кт, д 52</unrestricted_value><data><postal_code>308000</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Белгородская обл</region_with_type><region_type>обл</region_type><region_type_full>область</region_type_full><region>Белгородская</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Белгород</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Белгород</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>Гражданский пр-кт</street_with_type><street_type>пр-кт</street_type><street_type_full>проспект</street_type_full><street>Гражданский</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>52</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>818c6c95-8504-4975-ae31-28146fb85be8</fias_id><fias_level/><kladr_id>3100000100005690040</kladr_id><capital_marker/><okato>14401000000</okato><oktmo>14701000</oktmo><tax_office>3123</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г БИРОБИДЖАН)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г БИРОБИДЖАН)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БИРОБИДЖАН)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БИРОБИДЖАН)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г БИРОБИДЖАН)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г БИРОБИДЖАН)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Еврейская Аобл, г Биробиджан, ул Шолом-Алейхема, д 16</value><unrestricted_value>Еврейская Аобл, г Биробиджан, ул Шолом-Алейхема, д 16</unrestricted_value><data><postal_code>679016</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Еврейская Аобл</region_with_type><region_type>Аобл</region_type><region_type_full>автономная область</region_type_full><region>Еврейская</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Биробиджан</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Биробиджан</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>ул Шолом-Алейхема</street_with_type><street_type>ул</street_type><street_type_full>улица</street_type_full><street>Шолом-Алейхема</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>16</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>ba16a2dc-7ee1-4768-9501-c89a70c0d7d7</fias_id><fias_level/><kladr_id>7900000100002660103</kladr_id><capital_marker/><okato>99401000000</okato><oktmo>99701000</oktmo><tax_office>7901</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г БЛАГОВЕЩЕНСК)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г БЛАГОВЕЩЕНСК)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БЛАГОВЕЩЕНСК)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БЛАГОВЕЩЕНСК)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г БЛАГОВЕЩЕНСК)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г БЛАГОВЕЩЕНСК)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Амурская обл, г Благовещенск, ул Зейская, д 240</value><unrestricted_value>Амурская обл, г Благовещенск, ул Зейская, д 240</unrestricted_value><data><postal_code>675000</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Амурская обл</region_with_type><region_type>обл</region_type><region_type_full>область</region_type_full><region>Амурская</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Благовещенск</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Благовещенск</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>ул Зейская</street_with_type><street_type>ул</street_type><street_type_full>улица</street_type_full><street>Зейская</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>240</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>ee380858-4b50-4a0f-a625-8cd41ccd44a2</fias_id><fias_level/><kladr_id>2800000100000370082</kladr_id><capital_marker/><okato>10401000000</okato><oktmo>10701000</oktmo><tax_office>2801</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г БРЯНСК)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г БРЯНСК)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БРЯНСК)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БРЯНСК)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г БРЯНСК)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г БРЯНСК)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Брянская обл, г Брянск, пр-кт Ленина, д 10Б</value><unrestricted_value>Брянская обл, г Брянск, пр-кт Ленина, д 10Б</unrestricted_value><data><postal_code>241050</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Брянская обл</region_with_type><region_type>обл</region_type><region_type_full>область</region_type_full><region>Брянская</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Брянск</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Брянск</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>пр-кт Ленина</street_with_type><street_type>пр-кт</street_type><street_type_full>проспект</street_type_full><street>Ленина</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>10Б</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>76cd5c05-81a7-404f-8937-5365c2685746</fias_id><fias_level/><kladr_id>3200000100004480176</kladr_id><capital_marker/><okato>15401375000</okato><oktmo>15701000001</oktmo><tax_office>3257</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г ВЛАДИВОСТОК)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г ВЛАДИВОСТОК)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г ВЛАДИВОСТОК)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г ВЛАДИВОСТОК)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г ВЛАДИВОСТОК)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г ВЛАДИВОСТОК)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Приморский край, г Владивосток, ул Фонтанная, д 18</value><unrestricted_value>Приморский край, г Владивосток, ул Фонтанная, д 18</unrestricted_value><data><postal_code>690091</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Приморский край</region_with_type><region_type>край</region_type><region_type_full>край</region_type_full><region>Приморский</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Владивосток</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Владивосток</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>ул Фонтанная</street_with_type><street_type>ул</street_type><street_type_full>улица</street_type_full><street>Фонтанная</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>18</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>e8d8e1dd-5167-4b2b-9b1b-57145df015be</fias_id><fias_level/><kladr_id>2500000100005910012</kladr_id><capital_marker/><okato>05401376000</okato><oktmo>05701000</oktmo><tax_office>2540</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions></SuggestResponse>';  

SET NOCOUNT ON

DECLARE @idoc int

    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;   
      
    SELECT name_value, case when region_with_type is null then address_value else region_with_type end as [address]
    FROM       OPENXML (@idoc, '/SuggestResponse/suggestions')   
    WITH (name_value varchar(255) 'value',
          address_value    varchar(1000) 'data/address/value',
          region_with_type varchar(255) 'data/address/data/region_with_type'
        );
    EXEC sp_xml_removedocument @idoc; 
END
GO

ALTER proc [dbo].[usp_xml_dadata_get_orgdata_from_orgs](@doc varchar(max), @name_value varchar(255))
as 
BEGIN
--exec dbo.usp_xml_dadata_get_orgdata_from_orgs '<?xml version="1.0" encoding="windows-1251"?><SuggestResponse><suggestions><value>УМВД РОССИИ ПО ВОЛОГОДСКОЙ ОБЛАСТИ</value><unrestricted_value>УМВД РОССИИ ПО ВОЛОГОДСКОЙ ОБЛАСТИ</unrestricted_value><data><kpp>352501001</kpp><management><name>Федотов Виталий Петрович</name><post/></management><branch_type>MAIN</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf/><name><full_with_opf>УПРАВЛЕНИЕ МИНИСТЕРСТВА ВНУТРЕННИХ ДЕЛ РОССИЙСКОЙ ФЕДЕРАЦИИ ПО ВОЛОГОДСКОЙ ОБЛАСТИ</full_with_opf><short_with_opf>УМВД РОССИИ ПО ВОЛОГОДСКОЙ ОБЛАСТИ</short_with_opf><latin/><full>УПРАВЛЕНИЕ МИНИСТЕРСТВА ВНУТРЕННИХ ДЕЛ РОССИЙСКОЙ ФЕДЕРАЦИИ ПО ВОЛОГОДСКОЙ ОБЛАСТИ</full><short>УМВД РОССИИ ПО ВОЛОГОДСКОЙ ОБЛАСТИ</short></name><inn>3525041644</inn><ogrn>1033500037647</ogrn><okpo/><okved>84.24</okved><state><status>ACTIVE</status><actuality_date>1469491200000</actuality_date><registration_date>1039824000000</registration_date><liquidation_date/></state><address><value>Вологодская обл, г Вологда, ул Мира, д 30</value><unrestricted_value>Вологодская обл, г Вологда, ул Мира, д 30</unrestricted_value><data><postal_code>160001</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Вологодская обл</region_with_type><region_type>обл</region_type><region_type_full>область</region_type_full><region>Вологодская</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Вологда</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Вологда</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>ул Мира</street_with_type><street_type>ул</street_type><street_type_full>улица</street_type_full><street>Мира</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>30</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>faf4d608-f6fe-4b6d-afaa-40e2219515eb</fias_id><fias_level/><kladr_id>3500000100001640052</kladr_id><capital_marker/><okato>19401000000</okato><oktmo>19701000</oktmo><tax_office>3525</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions></SuggestResponse>', 'УМВД РОССИИ ПО ВОЛОГОДСКОЙ ОБЛАСТИ'

SET NOCOUNT ON

DECLARE @idoc int

    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;   
      
declare @parentid int;

    SELECT *
    INTO #t
    FROM OPENXML (@idoc, '/SuggestResponse/suggestions')

    select @parentid = parentid
    from #t
    where [id] = (select MIN(parentid) from #t where cast(IsNull([text],'') as varchar(255)) = @name_value);
    
    select
        1 + case when t3.localname = '#text' then 0 else 1 end 
        + case when IsNull(t4.localname,'#text') = '#text' then 0 else 1 end 
        + case when IsNull(t5.localname,'#text') = '#text' then 0 else 1 end 
        + case when IsNull(t6.localname,'#text') = '#text' then 0 else 1 end 
        as lvl, 
        t2.localname + case when t3.localname = '#text' then '' else '.' + t3.localname end 
        + case when IsNull(t4.localname,'#text') = '#text' then '' else '.' + IsNull(t4.localname,'') end 
        + case when IsNull(t5.localname,'#text') = '#text' then '' else '.' + IsNull(t5.localname,'') end 
        + case when IsNull(t6.localname,'#text') = '#text' then '' else '.' + IsNull(t6.localname,'') end 
        as key1, 
        cast(IsNull(t3.[text],'') as varchar(255)) + cast(IsNull(t4.[text],'') as varchar(255)) + cast(IsNull(t5.[text],'') as varchar(255)) + cast(IsNull(t6.[text],'') as varchar(255)) as text_value
    from #t t1
        inner join #t t2 on t1.id = t2.parentid
        left join #t t3 on t2.id = t3.parentid
        left join #t t4 on t3.id = t4.parentid
        left join #t t5 on t4.id = t5.parentid
        left join #t t6 on t5.id = t6.parentid
    where t1.parentid = 0 and t2.parentid = @parentid;

    drop table #t
        
    EXEC sp_xml_removedocument @idoc; 
END
GO

declare @t table(lvl int, key1 varchar(255), text_value varchar(255))

insert @t
exec dbo.usp_xml_dadata_get_orgdata_from_orgs '<?xml version="1.0" encoding="windows-1251"?><SuggestResponse><suggestions><value>ПАО СБЕРБАНК</value><unrestricted_value>ПАО СБЕРБАНК</unrestricted_value><data><kpp>775001001</kpp><management><name>Греф Герман Оскарович</name><post>Президент-председатель правления</post></management><branch_type>MAIN</branch_type><branch_count>91</branch_count><type>LEGAL</type><opf><code>12247</code><full>Публичное акционерное общество</full><short>ПАО</short></opf><name><full_with_opf>ПУБЛИЧНОЕ АКЦИОНЕРНОЕ ОБЩЕСТВО "СБЕРБАНК РОССИИ"</full_with_opf><short_with_opf>ПАО СБЕРБАНК</short_with_opf><latin/><full>СБЕРБАНК РОССИИ</full><short>СБЕРБАНК</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved>64.19</okved><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date>677376000000</registration_date><liquidation_date/></state><address><value>г Москва, ул Вавилова, д 19</value><unrestricted_value>г Москва, ул Вавилова, д 19</unrestricted_value><data><postal_code>117312</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>г Москва</region_with_type><region_type>г</region_type><region_type_full>город</region_type_full><region>Москва</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Москва</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Москва</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>ул Вавилова</street_with_type><street_type>ул</street_type><street_type_full>улица</street_type_full><street>Вавилова</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>19</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>93409d8c-d8d4-4491-838f-f9aa1678b5e6</fias_id><fias_level/><kladr_id>7700000000009240170</kladr_id><capital_marker/><okato>45293554000</okato><oktmo>45397000</oktmo><tax_office>7736</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г АБАКАН)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г АБАКАН)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г АБАКАН)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г АБАКАН)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г АБАКАН)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г АБАКАН)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>655017 РЕСПУБЛИКА ХАКАСИЯ ГОРОД АБАКАН УЛИЦА ПУШКИНА ДОМ 165 ЛИТЕР А ПОМЕЩЕНИЯ 2Н, 3Н</value><unrestricted_value>655017 РЕСПУБЛИКА ХАКАСИЯ ГОРОД АБАКАН УЛИЦА ПУШКИНА ДОМ 165 ЛИТЕР А ПОМЕЩЕНИЯ 2Н, 3Н</unrestricted_value><data/></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г АРХАНГЕЛЬСК)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г АРХАНГЕЛЬСК)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г АРХАНГЕЛЬСК)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г АРХАНГЕЛЬСК)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г АРХАНГЕЛЬСК)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г АРХАНГЕЛЬСК)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Архангельская обл, г Архангельск, пр-кт Ломоносова, д 137</value><unrestricted_value>Архангельская обл, г Архангельск, пр-кт Ломоносова, д 137</unrestricted_value><data><postal_code>163000</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Архангельская обл</region_with_type><region_type>обл</region_type><region_type_full>область</region_type_full><region>Архангельская</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Архангельск</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Архангельск</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>пр-кт Ломоносова</street_with_type><street_type>пр-кт</street_type><street_type_full>проспект</street_type_full><street>Ломоносова</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>137</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>f7eb063b-0095-47a1-b0d3-213656bcc07b</fias_id><fias_level/><kladr_id>2900000100002580159</kladr_id><capital_marker/><okato>11401000000</okato><oktmo>11701000</oktmo><tax_office>2901</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г АСТРАХАНЬ)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г АСТРАХАНЬ)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г АСТРАХАНЬ)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г АСТРАХАНЬ)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г АСТРАХАНЬ)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г АСТРАХАНЬ)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Астраханская обл, г Астрахань, ул Кирова, д 41</value><unrestricted_value>Астраханская обл, г Астрахань, ул Кирова, д 41</unrestricted_value><data><postal_code>414000</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Астраханская обл</region_with_type><region_type>обл</region_type><region_type_full>область</region_type_full><region>Астраханская</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Астрахань</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Астрахань</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>ул Кирова</street_with_type><street_type>ул</street_type><street_type_full>улица</street_type_full><street>Кирова</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>41</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>f76cf1a7-4494-4bc1-b696-e2569361550a</fias_id><fias_level/><kladr_id>3000000100040950047</kladr_id><capital_marker/><okato>12401000000</okato><oktmo>12701000</oktmo><tax_office>3015</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г БАРНАУЛ)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г БАРНАУЛ)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БАРНАУЛ)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БАРНАУЛ)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г БАРНАУЛ)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г БАРНАУЛ)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Алтайский край, г Барнаул, Комсомольский пр-кт, д 106А</value><unrestricted_value>Алтайский край, г Барнаул, Комсомольский пр-кт, д 106А</unrestricted_value><data><postal_code>656038</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Алтайский край</region_with_type><region_type>край</region_type><region_type_full>край</region_type_full><region>Алтайский</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Барнаул</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Барнаул</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>Комсомольский пр-кт</street_with_type><street_type>пр-кт</street_type><street_type_full>проспект</street_type_full><street>Комсомольский</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>106А</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>7a60a1df-0938-414d-8e6e-a141b9c48e97</fias_id><fias_level/><kladr_id>2200000100003180065</kladr_id><capital_marker/><okato>01401367000</okato><oktmo>01701000</oktmo><tax_office>2224</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г БЕЛГОРОД)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г БЕЛГОРОД)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БЕЛГОРОД)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БЕЛГОРОД)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г БЕЛГОРОД)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г БЕЛГОРОД)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Белгородская обл, г Белгород, Гражданский пр-кт, д 52</value><unrestricted_value>Белгородская обл, г Белгород, Гражданский пр-кт, д 52</unrestricted_value><data><postal_code>308000</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Белгородская обл</region_with_type><region_type>обл</region_type><region_type_full>область</region_type_full><region>Белгородская</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Белгород</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Белгород</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>Гражданский пр-кт</street_with_type><street_type>пр-кт</street_type><street_type_full>проспект</street_type_full><street>Гражданский</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>52</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>818c6c95-8504-4975-ae31-28146fb85be8</fias_id><fias_level/><kladr_id>3100000100005690040</kladr_id><capital_marker/><okato>14401000000</okato><oktmo>14701000</oktmo><tax_office>3123</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г БИРОБИДЖАН)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г БИРОБИДЖАН)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БИРОБИДЖАН)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БИРОБИДЖАН)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г БИРОБИДЖАН)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г БИРОБИДЖАН)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Еврейская Аобл, г Биробиджан, ул Шолом-Алейхема, д 16</value><unrestricted_value>Еврейская Аобл, г Биробиджан, ул Шолом-Алейхема, д 16</unrestricted_value><data><postal_code>679016</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Еврейская Аобл</region_with_type><region_type>Аобл</region_type><region_type_full>автономная область</region_type_full><region>Еврейская</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Биробиджан</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Биробиджан</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>ул Шолом-Алейхема</street_with_type><street_type>ул</street_type><street_type_full>улица</street_type_full><street>Шолом-Алейхема</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>16</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>ba16a2dc-7ee1-4768-9501-c89a70c0d7d7</fias_id><fias_level/><kladr_id>7900000100002660103</kladr_id><capital_marker/><okato>99401000000</okato><oktmo>99701000</oktmo><tax_office>7901</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г БЛАГОВЕЩЕНСК)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г БЛАГОВЕЩЕНСК)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БЛАГОВЕЩЕНСК)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БЛАГОВЕЩЕНСК)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г БЛАГОВЕЩЕНСК)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г БЛАГОВЕЩЕНСК)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Амурская обл, г Благовещенск, ул Зейская, д 240</value><unrestricted_value>Амурская обл, г Благовещенск, ул Зейская, д 240</unrestricted_value><data><postal_code>675000</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Амурская обл</region_with_type><region_type>обл</region_type><region_type_full>область</region_type_full><region>Амурская</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Благовещенск</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Благовещенск</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>ул Зейская</street_with_type><street_type>ул</street_type><street_type_full>улица</street_type_full><street>Зейская</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>240</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>ee380858-4b50-4a0f-a625-8cd41ccd44a2</fias_id><fias_level/><kladr_id>2800000100000370082</kladr_id><capital_marker/><okato>10401000000</okato><oktmo>10701000</oktmo><tax_office>2801</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г БРЯНСК)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г БРЯНСК)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БРЯНСК)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г БРЯНСК)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г БРЯНСК)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г БРЯНСК)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Брянская обл, г Брянск, пр-кт Ленина, д 10Б</value><unrestricted_value>Брянская обл, г Брянск, пр-кт Ленина, д 10Б</unrestricted_value><data><postal_code>241050</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Брянская обл</region_with_type><region_type>обл</region_type><region_type_full>область</region_type_full><region>Брянская</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Брянск</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Брянск</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>пр-кт Ленина</street_with_type><street_type>пр-кт</street_type><street_type_full>проспект</street_type_full><street>Ленина</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>10Б</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>76cd5c05-81a7-404f-8937-5365c2685746</fias_id><fias_level/><kladr_id>3200000100004480176</kladr_id><capital_marker/><okato>15401375000</okato><oktmo>15701000001</oktmo><tax_office>3257</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions><suggestions><value>ФИЛИАЛ ПАО СБЕРБАНК (Г ВЛАДИВОСТОК)</value><unrestricted_value>ФИЛИАЛ ПАО СБЕРБАНК (Г ВЛАДИВОСТОК)</unrestricted_value><data><kpp/><management/><branch_type>BRANCH</branch_type><branch_count>0</branch_count><type>LEGAL</type><opf><code>30002</code><full>ФИЛИАЛ</full><short>ФИЛИАЛ</short></opf><name><full_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г ВЛАДИВОСТОК)</full_with_opf><short_with_opf>ФИЛИАЛ ПАО СБЕРБАНК (Г ВЛАДИВОСТОК)</short_with_opf><latin/><full>ФИЛИАЛ ПАО СБЕРБАНК (Г ВЛАДИВОСТОК)</full><short>ФИЛИАЛ ПАО СБЕРБАНК (Г ВЛАДИВОСТОК)</short></name><inn>7707083893</inn><ogrn>1027700132195</ogrn><okpo/><okved/><state><status>ACTIVE</status><actuality_date>1473897600000</actuality_date><registration_date/><liquidation_date/></state><address><value>Приморский край, г Владивосток, ул Фонтанная, д 18</value><unrestricted_value>Приморский край, г Владивосток, ул Фонтанная, д 18</unrestricted_value><data><postal_code>690091</postal_code><country>Россия</country><region_fias_id/><region_kladr_id/><region_with_type>Приморский край</region_with_type><region_type>край</region_type><region_type_full>край</region_type_full><region>Приморский</region><area_fias_id/><area_kladr_id/><area_with_type/><area_type/><area_type_full/><area/><city_fias_id/><city_kladr_id/><city_with_type>г Владивосток</city_with_type><city_type>г</city_type><city_type_full>город</city_type_full><city>Владивосток</city><city_area/><city_district_fias_id/><city_district_kladr_id/><city_district_with_type/><city_district_type/><city_district_type_full/><city_district/><settlement_fias_id/><settlement_kladr_id/><settlement_with_type/><settlement_type/><settlement_type_full/><settlement/><street_fias_id/><street_kladr_id/><street_with_type>ул Фонтанная</street_with_type><street_type>ул</street_type><street_type_full>улица</street_type_full><street>Фонтанная</street><house_fias_id/><house_kladr_id/><house_type>д</house_type><house_type_full>дом</house_type_full><house>18</house><block_type/><block_type_full/><block/><flat_type/><flat_type_full/><flat/><flat_area/><square_meter_price/><flat_price/><postal_box/><fias_id>e8d8e1dd-5167-4b2b-9b1b-57145df015be</fias_id><fias_level/><kladr_id>2500000100005910012</kladr_id><capital_marker/><okato>05401376000</okato><oktmo>05701000</oktmo><tax_office>2540</tax_office><tax_office_legal/><timezone/><geo_lat/><geo_lon/><beltway_hit/><beltway_distance/><qc_geo/><qc_complete/><qc_house/><unparsed_parts/><qc/></data></address></data></suggestions></SuggestResponse>', 'ПАО СБЕРБАНК'

SELECT
 -- столбец (столбцы), значения из которого формируют заголовки строк2.
[value], [unrestricted_value], [data.kpp], [data.management.name], [data.management.post], [data.branch_type], [data.branch_count], [data.type], [data.opf.code], [data.opf.full], [data.opf.short], [data.name.full_with_opf], [data.name.short_with_opf], [data.name.latin], [data.name.full], [data.name.short], [data.inn], [data.ogrn], [data.okpo], [data.okved], [data.state.status], [data.state.actuality_date], [data.state.registration_date], [data.state.liquidation_date], [data.address.value], [data.address.unrestricted_value], [data.address.data.postal_code], [data.address.data.country], [data.address.data.region_fias_id], [data.address.data.region_kladr_id], [data.address.data.region_with_type], [data.address.data.region_type], [data.address.data.region_type_full], [data.address.data.region], [data.address.data.area_fias_id], [data.address.data.area_kladr_id], [data.address.data.area_with_type], [data.address.data.area_type], [data.address.data.area_type_full], [data.address.data.area], [data.address.data.city_fias_id], [data.address.data.city_kladr_id], [data.address.data.city_with_type], [data.address.data.city_type], [data.address.data.city_type_full], [data.address.data.city], [data.address.data.city_area], [data.address.data.city_district_fias_id], [data.address.data.city_district_kladr_id], [data.address.data.city_district_with_type], [data.address.data.city_district_type], [data.address.data.city_district_type_full], [data.address.data.city_district], [data.address.data.settlement_fias_id], [data.address.data.settlement_kladr_id], [data.address.data.settlement_with_type], [data.address.data.settlement_type], [data.address.data.settlement_type_full], [data.address.data.settlement], [data.address.data.street_fias_id], [data.address.data.street_kladr_id], [data.address.data.street_with_type], [data.address.data.street_type], [data.address.data.street_type_full], [data.address.data.street], [data.address.data.house_fias_id], [data.address.data.house_kladr_id], [data.address.data.house_type], [data.address.data.house_type_full], [data.address.data.house], [data.address.data.block_type], [data.address.data.block_type_full], [data.address.data.block], [data.address.data.flat_type], [data.address.data.flat_type_full], [data.address.data.flat], [data.address.data.flat_area], [data.address.data.square_meter_price], [data.address.data.flat_price], [data.address.data.postal_box], [data.address.data.fias_id], [data.address.data.fias_level], [data.address.data.kladr_id], [data.address.data.capital_marker], [data.address.data.okato], [data.address.data.oktmo], [data.address.data.tax_office], [data.address.data.tax_office_legal], [data.address.data.timezone], [data.address.data.geo_lat], [data.address.data.geo_lon], [data.address.data.beltway_hit], [data.address.data.beltway_distance], [data.address.data.qc_geo], [data.address.data.qc_complete], [data.address.data.qc_house], [data.address.data.unparsed_parts], [data.address.data.qc] -- значения из столбца, который указан в предложении type, 
-- формирующие заголовки столбцов 
FROM (select key1, text_value from @t) t
PIVOT -- формирование пивот-таблицы
(min(text_value) -- агрегатная функция, формирующая содержимое сводной таблицы
FOR key1 -- указывается столбец, -- уникальные значения в котором будут являться заголовками столбцов
IN ([value], [unrestricted_value], [data.kpp], [data.management.name], [data.management.post], [data.branch_type], [data.branch_count], [data.type], [data.opf.code], [data.opf.full], [data.opf.short], [data.name.full_with_opf], [data.name.short_with_opf], [data.name.latin], [data.name.full], [data.name.short], [data.inn], [data.ogrn], [data.okpo], [data.okved], [data.state.status], [data.state.actuality_date], [data.state.registration_date], [data.state.liquidation_date], [data.address.value], [data.address.unrestricted_value], [data.address.data.postal_code], [data.address.data.country], [data.address.data.region_fias_id], [data.address.data.region_kladr_id], [data.address.data.region_with_type], [data.address.data.region_type], [data.address.data.region_type_full], [data.address.data.region], [data.address.data.area_fias_id], [data.address.data.area_kladr_id], [data.address.data.area_with_type], [data.address.data.area_type], [data.address.data.area_type_full], [data.address.data.area], [data.address.data.city_fias_id], [data.address.data.city_kladr_id], [data.address.data.city_with_type], [data.address.data.city_type], [data.address.data.city_type_full], [data.address.data.city], [data.address.data.city_area], [data.address.data.city_district_fias_id], [data.address.data.city_district_kladr_id], [data.address.data.city_district_with_type], [data.address.data.city_district_type], [data.address.data.city_district_type_full], [data.address.data.city_district], [data.address.data.settlement_fias_id], [data.address.data.settlement_kladr_id], [data.address.data.settlement_with_type], [data.address.data.settlement_type], [data.address.data.settlement_type_full], [data.address.data.settlement], [data.address.data.street_fias_id], [data.address.data.street_kladr_id], [data.address.data.street_with_type], [data.address.data.street_type], [data.address.data.street_type_full], [data.address.data.street], [data.address.data.house_fias_id], [data.address.data.house_kladr_id], [data.address.data.house_type], [data.address.data.house_type_full], [data.address.data.house], [data.address.data.block_type], [data.address.data.block_type_full], [data.address.data.block], [data.address.data.flat_type], [data.address.data.flat_type_full], [data.address.data.flat], [data.address.data.flat_area], [data.address.data.square_meter_price], [data.address.data.flat_price], [data.address.data.postal_box], [data.address.data.fias_id], [data.address.data.fias_level], [data.address.data.kladr_id], [data.address.data.capital_marker], [data.address.data.okato], [data.address.data.oktmo], [data.address.data.tax_office], [data.address.data.tax_office_legal], [data.address.data.timezone], [data.address.data.geo_lat], [data.address.data.geo_lon], [data.address.data.beltway_hit], [data.address.data.beltway_distance], [data.address.data.qc_geo], [data.address.data.qc_complete], [data.address.data.qc_house], [data.address.data.unparsed_parts], [data.address.data.qc]) --указываются конкретные значения в столбце type, 
)pvt