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

Откуда:
Сообщений: 29
Извиняюсь заранее может за не совсем грамотное объяснение, так XML как снег на голову...

Образец xml файла(шапка), которому должен совпадать полученный мною XML:
<&xml version-"1.0" encoding="UTF8"?>
<issuance xmlns="http://www.w3schools.com"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.w3schools.com" sender="601701453">

Вопрос: как получить такое, ниже приведен мой запрос, основная загвоздка как запихнуть sender='601701453' сюда.
Отдельным тегом понимаю, так нет.

DECLARE @XML XML;
WITH XMLNAMESPACES (
'http://www.w3schools.com' as shemaLocation,
'132212321' as sender,
default 'http://www.w3schools.com')

SELECT @XML = (SELECT DataXML FROM
(SELECT [sender] as "sender/@sender"
,[number] as "general/number"
,[dateTransaction] as "general/dateTransaction"
,[documentType] as "general/documentType"
,[invoice] as "general/invoice"
,[dateCancellation] as "general/dateCancellation"
,[sendToRecipient] as "general/sendToRecipient"
,[providerStatus] as "provider/providerStatus"
,[dependentPerson] as "provider/dependentPerson"
,[residentsOfOffshore] as "provider/residentsOfOffshore"
,[specialDealGoods] as "provider/specialDealGoods"
,[bigCompany] as "provider/bigCompany"
,[countryCode] as "provider/countryCode"
,[unp] as "provider/unp"
,[branchCode] as "provider/branchCode"
,[name_post] as "provider/name_post"
,[address] as "provider/address"
,[principal number] as "provider/principal/number"
,[principal date] as "provider/principal/date"
,[vendor namber] as "provider/vendor/namber"
,[vendor date] as "provider/vendor/date"
,[declarations] as "provider/declarations"
,[dateRelease] as "provider/dateRelease"
,[dateActualExport] as "provider/dateActualExport"
,[taxes number] as "provider/taxes/number"
,[taxes date] as "provider/taxes/date"
,[recipientStatus] as "recipient/recipientStatus"
,[dependentPerson_pl] as "recipient/dependentPerson"
,[residentsOfOffshore_pl] as "recipient/residentsOfOffshore"
,[specialDealGoods_pl] as "recipient/specialDealGoods"
,[bigCompany_pl] as "recipient/bigCompany"
,[countryCode_pl] as "recipient/countryCode"
,[unp_pl] as "recipient/unp_pl"
,[branchCode_pl] as "recipient/branchCode"
,[name_pl] as "recipient/name"
,[address_pl] as "recipient/address"
,[declaration] as "recipient/declaration"
,[taxed number_pl] as "recipient/taxed/number"
,[taxed date_pl] as "recipient/taxed/date"
,[dateImport] as "recipient/dateImport"
,[countryCode_gro] as "senderReceiver/consignors/consignor/countryCode"
,[unp_gro] as "senderReceiver/consignors/consignor/unp"
,[name_gro] as "senderReceiver/consignors/consignor/name"
,[address_gro] as "senderReceiver/consignors/consignor/address"
,[countryCode_grp] as "senderReceiver/consignees/consignee/countryCode"
,[unp_grp] as "senderReceiver/consignees/consignee/unp"
,[name_grp] as "senderReceiver/consignees/consignee/name"
,[address_grp] as "senderReceiver/consignees/consignee/address"
,[contract number] as "deliveryCondirion/contact/number"
,[contract date] as "deliveryCondirion/contact/date"
,[typeDocument] as "deliveryCondirion/contact/documents/document/docType/code"
,[name Document] as "deliveryCondirion/contact/documents/document/docType/value"
,[date_post] as "deliveryCondirion/contact/documents/document/date"
,[blankCode] as "deliveryCondirion/contact/documents/document/blankCode"
,[seria] as "deliveryCondirion/contact/documents/document/seria"
,[number_post] as "deliveryCondirion/contact/documents/document/number"
,[description] as "deliveryCondirion/description"

,[tottalCost] as "roster/@tottalCost"
,[totalExcise] as "roster/@totalExcise"
,[totalVat] as "roster/@totalVat"
,[totalCostVat] as "roster/@totalCostVat"

,[number_tov] as "roster/rosterItem/number"
,[name_tov] as "roster/rosterItem/name"
,[code_tov] as "roster/rosterItem/code"
,[code_oced_tov] as "roster/rosterItem/code_oced"
,[units] as "roster/rosterItem/units"
,[count_tov] as "roster/rosterItem/count"
,[price_tov] as "roster/rosterItem/price"
,[costVat_tov_out] as "roster/rosterItem/cost"
,[summaExcise] as "roster/rosterItem/summaExcise"
,[rate] as "roster/rosterItem/vat/rate"
,[rateType] as "roster/rosterItem/vat/rateType"
,[summaVat] as "roster/rosterItem/vat/summaVat"
,[costVat] as "roster/rosterItem/costVat"
,[description_tov] as "roster/rosterItem/descriptions/description"

FROM [ZAVOD].[dbo].[UchetSchetFaktur]
FOR XML PATH (''), ROOT('issuance'),ELEMENTS XSINIL
)D(DataXML))
SELECT DataXML =@XML

У меня получается вот такая шапка
<issuance xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.w3schools.com" xmlns:sender="132212321" xmlns:shemaLocation="http://www.w3schools.com">
<sender sender="601701453 " />


Спасайте! спасибо!
20 май 16, 14:45    [19198576]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с доделать "шапку" XML  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Maxim696,

так?
DECLARE @InnerXML xml, @XML XML;

SELECT @InnerXML = 
(
  
  SELECT DataXML FROM
  (
    SELECT 
      [sender] as "sender/@sender"
      ,[number] as "general/number"
      ,[dateTransaction] as "general/dateTransaction"
      
      ...
      
    FROM [ZAVOD].[dbo].[UchetSchetFaktur]
  FOR XML PATH (''),ELEMENTS XSINIL
)D(DataXML));

WITH XMLNAMESPACES ( 
'http://www.w3schools.com' as shemaLocation,
default 'http://www.w3schools.com')
select @XML = (select '132212321' as [@sender], @innerXML as [*] for xml path('issuance'), type)
20 май 16, 15:40    [19199060]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с доделать "шапку" XML  [new]
Maxim696
Member

Откуда:
Сообщений: 29
daw, Огромное спасибо! Получилось почти как надо.
Единственное, получилось
<issuance xmlns="http://www.w3schools.com" xmlns:shemaLocation="http://www.w3schools.com" sender="132212321">
<issuance xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="">

а надо

<&xml version-"1.0" encoding="UTF8"?>
<issuance xmlns="http://www.w3schools.com"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.w3schools.com" sender="601701453">



имеет ли это разницу?
20 май 16, 15:54    [19199188]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с доделать "шапку" XML  [new]
Maxim696
Member

Откуда:
Сообщений: 29
daw, Может вы мне еще подскажите как быстро это все в файл сохранить?
Есть такое, но через sql не создается файл, а через командную строку создается, но не открывается.

DECLARE @cmd varchar( 2000)

Set @cmd = 'BCP "EXEC LENTA.PPO.sp_ExportXML" queryout c:\111.xml -C ACP -c -r -S ZAVOD-SRV -T -U sysadm -P LO(E#NHY^T%AQ!" '

exec master.dbo.xp_cmdshell @cmd
20 май 16, 16:11    [19199321]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить