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

Откуда: Msk
Сообщений: 581
Есть задача:
прочитать данные из xml, а затем эти данные записать в новый xml.
Для этого использую CTE но при формировании нового xml получаю ошибку:

DECLARE @xml XML
SELECT @xml = CONVERT(XML, BulkColumn, 2)
FROM   OPENROWSET(BULK '\\UBS_NT\FILES\xml\1.xml', SINGLE_BLOB) AS x

DECLARE @docHandle INT
EXEC sp_xml_preparedocument @docHandle OUTPUT,
     @xml 

--SELECT @xml  
;WITH cte AS (
SELECT inn AS inn_poluch,
       kpp AS kpp_poluch,
       NAME AS naimen_poluch,
       BankBIK,
       operatingAccountNumber AS acc_poluch,
       OrderDate AS date_trn,
       Amount,
       PaymentPurpose AS note_trn,
       [Year],
       [Month],
       AccountNumber AS 'ЛСИ'
FROM   OPENXML(
           @docHandle,
           'importNotificationsOfOrderExecution/NotificationOfOrderExecutionType',
           2
       )
       WITH (
           INN NVARCHAR(20) './RecipientInfo/PaymentInformation/RecipientINN',
           KPP NVARCHAR(20) './RecipientInfo/PaymentInformation/RecipientKPP',
           NAME NVARCHAR(200) 
           './RecipientInfo/PaymentInformation/PaymentRecipient',
           BankBIK NVARCHAR(20) './RecipientInfo/PaymentInformation/BankBIK',
           operatingAccountNumber NVARCHAR(20) 
           './RecipientInfo/PaymentInformation/operatingAccountNumber',
           OrderDate DATETIME './OrderInfo/OrderDate',
           Amount FLOAT './OrderInfo/Amount',
           PaymentPurpose NVARCHAR(250) './OrderInfo/PaymentPurpose',
           [YEAR] INT './OrderInfo/Year',
           [Month] INT './OrderInfo/Month',
           AccountNumber NVARCHAR(20) './OrderInfo/AccountNumber'
       )
)

WITH XMLNAMESPACES ('http://www.ubs/system/base' as u)
SELECT * FROM cte FOR XML PATH('u:UBS_TRANSFER')
       




Сообщение 156, уровень 15, состояние 1, строка 49
Incorrect syntax near the keyword 'WITH'.
Сообщение 319, уровень 15, состояние 1, строка 49
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
8 авг 16, 16:45    [19516735]     Ответить | Цитировать Сообщить модератору
 Re: xml и пространство имен в cte  [new]
o-o
Guest
не суйте with в другой with,
а воткните код напрямую типа так:
DECLARE @xml XML
SELECT @xml = CONVERT(XML, BulkColumn, 2)
FROM   OPENROWSET(BULK '\\UBS_NT\FILES\xml\1.xml', SINGLE_BLOB) AS x

DECLARE @docHandle INT
EXEC sp_xml_preparedocument @docHandle OUTPUT,
     @xml; 

--SELECT @xml  

WITH XMLNAMESPACES ('http://www.ubs/system/base' as u)
SELECT * FROM 

(
SELECT inn AS inn_poluch,
       kpp AS kpp_poluch,
       NAME AS naimen_poluch,
       BankBIK,
       operatingAccountNumber AS acc_poluch,
       OrderDate AS date_trn,
       Amount,
       PaymentPurpose AS note_trn,
       [Year],
       [Month],
       AccountNumber AS 'ЛСИ'
FROM   OPENXML(
           @docHandle,
           'importNotificationsOfOrderExecution/NotificationOfOrderExecutionType',
           2
       )
       WITH (
           INN NVARCHAR(20) './RecipientInfo/PaymentInformation/RecipientINN',
           KPP NVARCHAR(20) './RecipientInfo/PaymentInformation/RecipientKPP',
           NAME NVARCHAR(200) 
           './RecipientInfo/PaymentInformation/PaymentRecipient',
           BankBIK NVARCHAR(20) './RecipientInfo/PaymentInformation/BankBIK',
           operatingAccountNumber NVARCHAR(20) 
           './RecipientInfo/PaymentInformation/operatingAccountNumber',
           OrderDate DATETIME './OrderInfo/OrderDate',
           Amount FLOAT './OrderInfo/Amount',
           PaymentPurpose NVARCHAR(250) './OrderInfo/PaymentPurpose',
           [YEAR] INT './OrderInfo/Year',
           [Month] INT './OrderInfo/Month',
           AccountNumber NVARCHAR(20) './OrderInfo/AccountNumber'
       )
)

cte 
FOR XML PATH('u:UBS_TRANSFER')
8 авг 16, 17:00    [19516801]     Ответить | Цитировать Сообщить модератору
 Re: xml и пространство имен в cte  [new]
NewIvanovec
Member

Откуда: Msk
Сообщений: 581
o-o,

Спасибо. Всё работает.

Как ещё можно добавить сюда
<?xml version="1.0" encoding="windows-1251" ?>
8 авг 16, 17:05    [19516833]     Ответить | Цитировать Сообщить модератору
 Re: xml и пространство имен в cte  [new]
daw
Member

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

BOL
When you use the WITH XMLNAMESPACES clause in a statement that also includes a common table expression, the WITH XMLNAMESPACES clause must precede the common table expression in the statement.


;WITH XMLNAMESPACES ('http://www.ubs/system/base' as u)
, cte AS (
SELECT inn AS inn_poluch,
       kpp AS kpp_poluch,
       NAME AS naimen_poluch,
       BankBIK,
       operatingAccountNumber AS acc_poluch,
       OrderDate AS date_trn,
       Amount,
       PaymentPurpose AS note_trn,
       [Year],
       [Month],
       AccountNumber AS 'ЛСИ'
FROM   OPENXML(
           @docHandle,
           'importNotificationsOfOrderExecution/NotificationOfOrderExecutionType',
           2
       )
       WITH (
           INN NVARCHAR(20) './RecipientInfo/PaymentInformation/RecipientINN',
           KPP NVARCHAR(20) './RecipientInfo/PaymentInformation/RecipientKPP',
           NAME NVARCHAR(200) 
           './RecipientInfo/PaymentInformation/PaymentRecipient',
           BankBIK NVARCHAR(20) './RecipientInfo/PaymentInformation/BankBIK',
           operatingAccountNumber NVARCHAR(20) 
           './RecipientInfo/PaymentInformation/operatingAccountNumber',
           OrderDate DATETIME './OrderInfo/OrderDate',
           Amount FLOAT './OrderInfo/Amount',
           PaymentPurpose NVARCHAR(250) './OrderInfo/PaymentPurpose',
           [YEAR] INT './OrderInfo/Year',
           [Month] INT './OrderInfo/Month',
           AccountNumber NVARCHAR(20) './OrderInfo/AccountNumber'
       )
)

SELECT * FROM cte FOR XML PATH('u:UBS_TRANSFER')
8 авг 16, 17:06    [19516838]     Ответить | Цитировать Сообщить модератору
 Re: xml и пространство имен в cte  [new]
NewIvanovec
Member

Откуда: Msk
Сообщений: 581
daw
NewIvanovec,

BOL
When you use the WITH XMLNAMESPACES clause in a statement that also includes a common table expression, the WITH XMLNAMESPACES clause must precede the common table expression in the statement.


;WITH XMLNAMESPACES ('http://www.ubs/system/base' as u)
, cte AS (
SELECT inn AS inn_poluch,
       kpp AS kpp_poluch,
       NAME AS naimen_poluch,
       BankBIK,
       operatingAccountNumber AS acc_poluch,
       OrderDate AS date_trn,
       Amount,
       PaymentPurpose AS note_trn,
       [Year],
       [Month],
       AccountNumber AS 'ЛСИ'
FROM   OPENXML(
           @docHandle,
           'importNotificationsOfOrderExecution/NotificationOfOrderExecutionType',
           2
       )
       WITH (
           INN NVARCHAR(20) './RecipientInfo/PaymentInformation/RecipientINN',
           KPP NVARCHAR(20) './RecipientInfo/PaymentInformation/RecipientKPP',
           NAME NVARCHAR(200) 
           './RecipientInfo/PaymentInformation/PaymentRecipient',
           BankBIK NVARCHAR(20) './RecipientInfo/PaymentInformation/BankBIK',
           operatingAccountNumber NVARCHAR(20) 
           './RecipientInfo/PaymentInformation/operatingAccountNumber',
           OrderDate DATETIME './OrderInfo/OrderDate',
           Amount FLOAT './OrderInfo/Amount',
           PaymentPurpose NVARCHAR(250) './OrderInfo/PaymentPurpose',
           [YEAR] INT './OrderInfo/Year',
           [Month] INT './OrderInfo/Month',
           AccountNumber NVARCHAR(20) './OrderInfo/AccountNumber'
       )
)

SELECT * FROM cte FOR XML PATH('u:UBS_TRANSFER')


Тоже большое спасибо за этот вариант!
8 авг 16, 17:15    [19516889]     Ответить | Цитировать Сообщить модератору
 Re: xml и пространство имен в cte  [new]
Redbor
Member

Откуда: Москва
Сообщений: 289
daw
NewIvanovec,
;WITH XMLNAMESPACES ('http://www.ubs/system/base' as u)
...
           AccountNumber NVARCHAR(20) './OrderInfo/AccountNumber'
       )
)

SELECT * FROM cte FOR XML PATH('u:UBS_TRANSFER')

Народ объясните, пожалуйста, что за идентификатор "UBS_TRANSFER" используется в последней строке? Где он описан?
6 июл 17, 15:01    [20619346]     Ответить | Цитировать Сообщить модератору
 Re: xml и пространство имен в cte  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
Redbor
daw
NewIvanovec,
;WITH XMLNAMESPACES ('http://www.ubs/system/base' as u)
...
           AccountNumber NVARCHAR(20) './OrderInfo/AccountNumber'
       )
)

SELECT * FROM cte FOR XML PATH('u:UBS_TRANSFER')

Народ объясните, пожалуйста, что за идентификатор "UBS_TRANSFER" используется в последней строке? Где он описан?


Он описан в справке по FOR XML
6 июл 17, 15:58    [20619652]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить