Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
Максим420
Member

Откуда:
Сообщений: 39
надо чтобы в "addresses" было два типа "address"
Вот так правильно, а у меня
+
<?xml version="1.0"?>
-<soapenv:Envelope xmlns:ap="http://mbtc.ru/afs/application" xmlns:afs="http://mbtc.ru/afs"xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header/>
-<soapenv:Body>

-<afs:afsRequest>
-<afs:auth>
<afs:login>admin</afs:login>
<afs:password>admin</afs:password>
</afs:auth>
<afs:action>match</afs:action>
<afs:ruleSetId>STOP_LIST_1</afs:ruleSetId>

-<Application>
<id>DP:200510</id>
<version>1</version>
<date>12.01.2017</date>
-<app>
<bank>1</bank>

-<applicant>
<id>200510</id>
-<person>
<lastName>БУРГАНОВА</lastName>
<firstName>РУБИНА</firstName>
<secondName>ХАСАНЖАНОВНА</secondName>
<birthDate>18.05.1984</birthDate>
<birthPlace>ГОР.КРАСНОТУРЬИНСК СВЕРДЛОВСКОЙ ОБЛ.</birthPlace>
<sex>0</sex>
</person>

-<doc>
<type>21</type>
<seriesNumber>6512557824</seriesNumber>
<date>07.08.2013</date>
<issued> ОТДЕЛЕНИЕМ УФМС РОССИИ ПО СВЕРДЛОВСКОЙ ОБЛ. В ГОРОДЕКРАСНОТУРЬИНСК </issued>
</doc>

-<addresses>
-<address>
<type>1</type>
<region>66</region>
<postalCode>624465</postalCode>
<city>КРАСНОТУРЬИНСК</city>
<street>НОВАЯ УЛ</street>
<house>34</house>
<flat>1</flat>
</address>

-<address>
<type>2</type>
<region>66</region>
<postalCode>624465</postalCode>
<city>КРАСНОТУРЬИНСК</city>
<street>НОВАЯ УЛ</street>
<house>34</house>
<flat>1</flat>
</address>
</addresses>

-<phone>
<type>3</type>
<number>9089128111</number>
</phone>

</applicant>
</app>
</Application>
</afs:afsRequest>
</soapenv:Body>
</soapenv:Envelope>

Вот что получается
+
<afsRequest xmlns="http://mbtc.ru/afs/">
  <auth>
    <login>LoyaltyWS</login>
    <password>LoyaltyWS1!</password>
  </auth>
  <action>match</action>
  <ruleSetId>STOP_LIST_1</ruleSetId>
  <Application>
    <id>DP:0</id>
    <version>1</version>
    <date>2017-02-27</date>
    <app>
      <bank>1</bank>
      <applicant>
        <id>0</id>
        <person>
          <lastName>ПОЛОСКОВА</lastName>
          <firstName>НАТАЛЬЯ</firstName>
          <secondName>НИКОЛАЕВНА</secondName>
          <birthDate>13.01.1977</birthDate>
          <sex>0</sex>
        </person>

        <doc>
          <type>21</type>
          <seriesNumber>7503287549</seriesNumber>
          <date>03.06.2003</date>
        </doc>

        <addresses>

          <address>

            <type>1</type>
            <region>74</region>
            <postalCode>454084</postalCode>
            <city>Челябинск г</city>
            <street>Каслинская ул</street>
            <house>52</house>
            <corp>0</corp>
            <flat>80</flat>

            <type>2</type>
            <region>74</region>
            <postalCode>454084</postalCode>
            <city>Челябинск г</city>
            <street>Каслинская ул</street>
            <house>52</house>
            <corp>0</corp>
            <flat>80</flat>

          </address>

        </addresses>

        <phone>
          <type>1</type>
          <number>3517915228</number>
          <type>2</type>
          <number>3517461806</number>
          <type>3</type>
          <number>9090761150</number>
          <type>5</type>
          <number>3512461806</number>
        </phone>

      </applicant>
    </app>
  </Application>
</afsRequest>

И с телефоном беда толжен отображаться только один тип
А вот сам SQL
+
@Body= (
SELECT	 
'LoyaltyWS' as [auth/login], 
'LoyaltyWS1!' as [auth/password], 
'match' as [action],
'STOP_LIST_1' as [ruleSetId],
N'DP:'+@BP_ID as [Application/id],
'1' as [Application/version],
CONVERT(nvarchar(20),CONVERT(date,GETDATE(),104)) as [Application/date],
'1' as [Application/app/bank],
@BP_ID as [Application/app/applicant/id],
@lastName as [Application/app/applicant/person/lastName],
@firstName as [Application/app/applicant/person/firstName],
@secondName as [Application/app/applicant/person/secondName],
CONVERT(nvarchar(20),@birthDate,104) as [Application/app/applicant/person/birthDate],
@sex as [Application/app/applicant/person/sex],						
'21' as [Application/app/applicant/doc/type],
@seriesNumber as [Application/app/applicant/doc/seriesNumber],
CONVERT(nvarchar(20),@docdate,104) as [Application/app/applicant/doc/date],
--'660-666' as [Application/app/applicant/doc/issued]

'1' as [Application/app/applicant/addresses/address/type],
@regionReg as [Application/app/applicant/addresses/address/region],
@indexReg as [Application/app/applicant/addresses/address/postalCode],
@cityReg as [Application/app/applicant/addresses/address/city],
@streetReg as [Application/app/applicant/addresses/address/street],
@houseReg as [Application/app/applicant/addresses/address/house],	
@corpReg as [Application/app/applicant/addresses/address/corp],
@flatReg as [Application/app/applicant/addresses/address/flat] ,

'2' as [Application/app/applicant/addresses/address/type],
@regionLife as [Application/app/applicant/addresses/address/region],
@indexLife as [Application/app/applicant/addresses/address/postalCode],
@cityLife as [Application/app/applicant/addresses/address/city],
@streetLife as [Application/app/applicant/addresses/address/street],
@houseLife as [Application/app/applicant/addresses/address/house],
@corpLife as [Application/app/applicant/addresses/address/corp],
@flatLife as [Application/app/applicant/addresses/address/flat], 

'1' as [Application/app/applicant/phone/type],
@housetel as [Application/app/applicant/phone/number],
'2' as [Application/app/applicant/phone/type],
@worktel as [Application/app/applicant/phone/number],
'3' as [Application/app/applicant/phone/type],
@mobiltel as [Application/app/applicant/phone/number],
--'4' as [Application/app/applicant/phone/type],
'5' as [Application/app/applicant/phone/type],
@contacttel as [Application/app/applicant/phone/number]

FOR	XML Path('afsRequest'),Type)


Сообщение было отредактировано: 27 фев 17, 14:55
27 фев 17, 07:38    [20245683]     Ответить | Цитировать Сообщить модератору
 Re: Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
Максим420
Member

Откуда:
Сообщений: 39
Помогите пожалуйста , или литературу подскажите , очень хочу разобраться и благодарю за помощь
27 фев 17, 10:49    [20246291]     Ответить | Цитировать Сообщить модератору
 Re: Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Максим420
'1' as [Application/app/applicant/addresses/address/type],
@regionReg as [Application/app/applicant/addresses/address/region],
@indexReg as [Application/app/applicant/addresses/address/postalCode],
@cityReg as [Application/app/applicant/addresses/address/city],
@streetReg as [Application/app/applicant/addresses/address/street],
@houseReg as [Application/app/applicant/addresses/address/house],
@corpReg as [Application/app/applicant/addresses/address/corp],
@flatReg as [Application/app/applicant/addresses/address/flat] ,

'2' as [Application/app/applicant/addresses/address/type],
@regionLife as [Application/app/applicant/addresses/address/region],
@indexLife as [Application/app/applicant/addresses/address/postalCode],
@cityLife as [Application/app/applicant/addresses/address/city],
@streetLife as [Application/app/applicant/addresses/address/street],
@houseLife as [Application/app/applicant/addresses/address/house],
@corpLife as [Application/app/applicant/addresses/address/corp],
@flatLife as [Application/app/applicant/addresses/address/flat],
[/SRC]
Нужно примерно так:
declare @v1 int = 1, @v2 int = 2;

select
 (select v from (values (@v1), (@v2)) t(v) for xml path('Item'), type) as Items
for xml path('Root'), type;
27 фев 17, 11:33    [20246535]     Ответить | Цитировать Сообщить модератору
 Re: Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
Максим420
Member

Откуда:
Сообщений: 39
invm,так не получается или я не могу разобраться ... ругается на единицу по типу телефон , и выводит запись новую а не старый тип объеденяет
27 фев 17, 14:02    [20247175]     Ответить | Цитировать Сообщить модератору
 Re: Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Максим420,

Предлагаете угадать что как вы делаете?
27 фев 17, 14:20    [20247260]     Ответить | Цитировать Сообщить модератору
 Re: Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
Максим420
Member

Откуда:
Сообщений: 39
invm,

+
DECLARE @BP_ID nvarchar(10)='0'
DECLARE @lastName nvarchar(20) 
DECLARE @firstName nvarchar(20) 
DECLARE @secondName nvarchar(20) 
DECLARE @birthDate datetime 
DECLARE @sex int 
DECLARE @seriesNumber nvarchar(10) 
DECLARE @docdate datetime
DECLARE @regionReg int
DECLARE @indexReg int
DECLARE @cityReg nvarchar(20)
DECLARE @streetReg nvarchar(20)
DECLARE @houseReg int
DECLARE @corpReg int
DECLARE @flatReg int
DECLARE @regionLife int
DECLARE @indexLife int
DECLARE @cityLife nvarchar(20)
DECLARE @streetLife nvarchar(20)
DECLARE @houseLife int
DECLARE @corpLife int
DECLARE @flatLife int
DECLARE @housetel nvarchar(20)
DECLARE @worktel nvarchar(20)
DECLARE @mobiltel nvarchar(20)
DECLARE @contacttel nvarchar(20)
DECLARE @AfsMessage nvarchar(1000)

SELECT 
@lastName=a.ANKETA_FAM, 
@firstName=a.ANKETA_IM, 
@secondName=a.ANKETA_OT, 
@birthDate=a.ANKETA_BIRTH_DATE, 
@sex=CASE a.ANKETA_SEX WHEN N'Ж' THEN 0 ELSE 1 END, 
@seriesNumber=ISNULL(SUBSTRING(ANKETA_SERIESPASSPORT,1,2)+SUBSTRING(ANKETA_SERIESPASSPORT,4,2)+ANKETA_NUMBERPASSPORT,''), 
@docdate=ISNULL(ANKETA_DATEPASSPORT,'19000101'),
@regionReg=ISNULL([ANKETA_REGION_REGISTR],''),
@indexReg=ISNULL([UBRR_ANKETA_REGINDEX],''),
@cityReg=ISNULL([UBRR_ANKETA_REGCITY],''),
@streetReg=ISNULL([UBRR_ANKETA_REGSTREET],''),
@houseReg=ISNULL([UBRR_ANKETA_REGHOUSE],''),
@corpReg=ISNULL([UBRR_ANKETA_REGCORP],''),
@flatReg=ISNULL([UBRR_ANKETA_REGFLAT],''),
@regionLife=ISNULL([ANKETA_REGION_RESIDE],''),
@indexLife=ISNULL([UBRR_ANKETA_LIFEINDEX],''),
@cityLife=ISNULL([UBRR_ANKETA_LIFECITY],''),
@streetLife=ISNULL([UBRR_ANKETA_LIFESTREET],''),
@houseLife=ISNULL([UBRR_ANKETA_LIFEHOUSE],''),
@corpLife=ISNULL([UBRR_ANKETA_LIFECORP],''),
@flatLife=ISNULL([UBRR_ANKETA_LIFEFLAT],'') ,
@housetel=ISNULL(REPLACE([ANKETA_HOUSETEL],'_',''),''),
@worktel=ISNULL(REPLACE([ANKETA_WORKTEL],'_',''),''),
@mobiltel=ISNULL(REPLACE([ANKETA_MOBILTEL],'_',''),''),
@contacttel=ISNULL(REPLACE([UBRR_ANKETA_ORG_CONTACTTEL],'_',''),'') 
FROM port.Ankets a
WHERE ANKETA_ID=200510 -- AND a.ANKETA_FAM='ФАМ';

DECLARE	 @URL		SysName
,@Body xml
DECLARE @v1 int = 1
, @v2 int = 2

-- Создание запроса
;WITH XMLNAMESPACES (DEFAULT 'http://mbtc.ru/afs/')
SELECT	 @URL	= 'http://sas-afs.lan.ubrr.ru:8080/afs/ws/ubrrService/?wsdl'
	,@Body		= (
SELECT	 
'LoyaltyWS' as [auth/login], 
'LoyaltyWS1!' as [auth/password], 
'match' as [action],
'STOP_LIST_1' as [ruleSetId],
N'DP:'+@BP_ID as [Application/id],
'1' as [Application/version],
CONVERT(nvarchar(20),CONVERT(date,GETDATE(),104)) as [Application/date],
'1' as [Application/app/bank],
@BP_ID as [Application/app/applicant/id],
@lastName as [Application/app/applicant/person/lastName],
@firstName as [Application/app/applicant/person/firstName],
@secondName as [Application/app/applicant/person/secondName],
CONVERT(nvarchar(20),@birthDate,104) as [Application/app/applicant/person/birthDate],
@sex as [Application/app/applicant/person/sex],						
'21' as [Application/app/applicant/doc/type],
@seriesNumber as [Application/app/applicant/doc/seriesNumber],
CONVERT(nvarchar(20),@docdate,104) as [Application/app/applicant/doc/date],
--'660-666' as [Application/app/applicant/doc/issued]

'1' as [Application/app/applicant/addresses/address/type],
@regionReg as [Application/app/applicant/addresses/address/region],
@indexReg as [Application/app/applicant/addresses/address/postalCode],
@cityReg as [Application/app/applicant/addresses/address/city],
@streetReg as [Application/app/applicant/addresses/address/street],
@houseReg as [Application/app/applicant/addresses/address/house],	
@corpReg as [Application/app/applicant/addresses/address/corp],
@flatReg as [Application/app/applicant/addresses/address/flat] ,

'2' as [Application/app/applicant/addresses/address/type],
@regionLife as [Application/app/applicant/addresses/address/region],
@indexLife as [Application/app/applicant/addresses/address/postalCode],
@cityLife as [Application/app/applicant/addresses/address/city],
@streetLife as [Application/app/applicant/addresses/address/street],
@houseLife as [Application/app/applicant/addresses/address/house],
@corpLife as [Application/app/applicant/addresses/address/corp],
@flatLife as [Application/app/applicant/addresses/address/flat], 

(select v from (values (@v1), (@v2)) t(v) for xml path('address'), type) as addresses

'1' as [Application/app/applicant/phone/type],  --вот тут ругается 
@housetel as [Application/app/applicant/phone/number],
'2' as [Application/app/applicant/phone/type],
@worktel as [Application/app/applicant/phone/number],
'3' as [Application/app/applicant/phone/type],
@mobiltel as [Application/app/applicant/phone/number],
--'4' as [Application/app/applicant/phone/type],
'5' as [Application/app/applicant/phone/type],
@contacttel as [Application/app/applicant/phone/number]

FOR	XML Path('afsRequest'),Type)
SELECT @Body
-- Вызов WebMethod-а
EXEC	dbo.sp_SOAPMethodCall
		 @URL
		,NULL
		,@Body		OUT
-- Результат
;
SET @AfsMessage=ISNULL(@Body.value('declare namespace p1="http://mbtc.ru/afs";
(/p1:afsResponse/p1:matchResult/p1:match/p1:description)[1]','nvarchar(1000)'),N'По клиенту ничего не найдено.');
with xmlnamespaces(default 'http://mbtc.ru/afs')
select
@AfsMessage=stuff(t.n.query('for $d in (match/description) return concat("; ", $d/text()[1])').value('.', 'nvarchar(max)'), 1, 2, '')
from
 @Body.nodes('/afsResponse/matchResult') t(n);

вот такой код со временем получился

Модератор: Убирайте ваши портянки еще и в тег spoiler, пожалуйста


Сообщение было отредактировано: 27 фев 17, 14:56
27 фев 17, 14:40    [20247341]     Ответить | Цитировать Сообщить модератору
 Re: Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Максим420,

Вы даже не потрудились попробовать понять как работает предложенный пример. Просто тупо вставили его текст в свой запрос.
27 фев 17, 15:03    [20247443]     Ответить | Цитировать Сообщить модератору
 Re: Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
Максим420
Member

Откуда:
Сообщений: 39
invm,
ну как сказать, я пробовал разные конструкции.. я даже не знаю какой адекватный вопрос в инете написать , "как создать тело XML с помощью sql два ордера в одном теле" .... с чего начать
27 фев 17, 15:10    [20247481]     Ответить | Цитировать Сообщить модератору
 Re: Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
Максим420
Member

Откуда:
Сообщений: 39
Максим420,
как я понимаю место '1' и '2' я ставлю переменные свои
DECLARE @URL SysName,@Body xml
DECLARE @v1 int = 1, @v2 int = 2;
WITH XMLNAMESPACES (DEFAULT 'http://mbtc.ru/afs/')
SELECT @URL = 'http://sas-afs.lan.ubrr.ru:8080/afs/ws/ubrrService/?wsdl'
	,@Body = (
SELECT	 
'LoyaltyWS' as [auth/login], 
'LoyaltyWS1!' as [auth/password], 
'match' as [action],
'STOP_LIST_1' as [ruleSetId],
N'DP:'+@BP_ID as [Application/id],
'1' as [Application/version],
CONVERT(nvarchar(20),CONVERT(date,GETDATE(),104)) as [Application/date],
'1' as [Application/app/bank],
@BP_ID as [Application/app/applicant/id],
@lastName as [Application/app/applicant/person/lastName],
@firstName as [Application/app/applicant/person/firstName],
@secondName as [Application/app/applicant/person/secondName],
CONVERT(nvarchar(20),@birthDate,104) as [Application/app/applicant/person/birthDate],
@sex as [Application/app/applicant/person/sex],						
'21' as [Application/app/applicant/doc/type],
@seriesNumber as [Application/app/applicant/doc/seriesNumber],
CONVERT(nvarchar(20),@docdate,104) as [Application/app/applicant/doc/date],
--'660-666' as [Application/app/applicant/doc/issued]

@v1 as [Application/app/applicant/addresses/address/type],
@regionReg as [Application/app/applicant/addresses/address/region],
@indexReg as [Application/app/applicant/addresses/address/postalCode],
@cityReg as [Application/app/applicant/addresses/address/city],
@streetReg as [Application/app/applicant/addresses/address/street],
@houseReg as [Application/app/applicant/addresses/address/house],	
@corpReg as [Application/app/applicant/addresses/address/corp],
@flatReg as [Application/app/applicant/addresses/address/flat] ,

@v2 as [Application/app/applicant/addresses/address/type],
@regionLife as [Application/app/applicant/addresses/address/region],
@indexLife as [Application/app/applicant/addresses/address/postalCode],
@cityLife as [Application/app/applicant/addresses/address/city],
@streetLife as [Application/app/applicant/addresses/address/street],
@houseLife as [Application/app/applicant/addresses/address/house],
@corpLife as [Application/app/applicant/addresses/address/corp],
@flatLife as [Application/app/applicant/addresses/address/flat], 

(select type from (values (@v1), (@v2)) t(type) for xml path('address'), type) as addresses,

'1' as [Application/app/applicant/phone/type],
@housetel as [Application/app/applicant/phone/number],
'2' as [Application/app/applicant/phone/type],
@worktel as [Application/app/applicant/phone/number],
'3' as [Application/app/applicant/phone/type],
@mobiltel as [Application/app/applicant/phone/number],
--'4' as [Application/app/applicant/phone/type],
'5' as [Application/app/applicant/phone/type],
@contacttel as [Application/app/applicant/phone/number]

FOR	XML Path('afsRequest'),Type)



Ошибка при выполнении метода "http://mbtc.ru/afs/afsRequest" в "soapenv:Client": RuntimeException: Error parsing application: java.lang.RuntimeException: empty application id: java.lang.RuntimeException: empty
27 фев 17, 15:41    [20247643]     Ответить | Цитировать Сообщить модератору
 Re: Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
Максим420
Member

Откуда:
Сообщений: 39
invm,
о чём может говорить эта ошибка тоже представления не имею (
27 фев 17, 15:45    [20247669]     Ответить | Цитировать Сообщить модератору
 Re: Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
Максим420
Member

Откуда:
Сообщений: 39
invm,
я прошу прощения но уже вот два дня в поисках информации и видно до меня туго доходит (
1 мар 17, 10:40    [20253333]     Ответить | Цитировать Сообщить модератору
 Re: Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
Максим420
Member

Откуда:
Сообщений: 39
invm,
я прошу прощения но уже вот два дня в поисках информации и видно до меня туго доходит (
1 мар 17, 15:37    [20254568]     Ответить | Цитировать Сообщить модератору
 Re: Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
...
(
 select
  a.*
 from
  (
   values
    (1, @regionReg, @indexReg, @cityReg, @streetReg, @houseReg, @corpReg, @flatReg),
    (2, @LifeionLife, @indexLife, @cityLife, @streetLife, @houseLife, @corpLife, @flatLife)
  ) a([type], region, postalCode, city, street, house, corp, flat)
 for xml path('address'), type
) as [Application/app/applicant/addresses]
...
1 мар 17, 16:15    [20254738]     Ответить | Цитировать Сообщить модератору
 Re: Редактировать XML в SQL (тело в теле с одинаковым именем)  [new]
Максим420
Member

Откуда:
Сообщений: 39
invm,
Большое спасибо о великий гуру ! буду стараться и познавать !!!
2 мар 17, 15:11    [20257758]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить