有接收到的XML文件,我从中获得了货物框架。然后,我生成一个答案XML,我想在其中使用源XML的一些块。这就是为什么我尝试将XML实例的"货币"变量插入到另一个XML实例中的原因。但是没有错误,但是它不会插入值...怎么了?
DECLARE @source_vsd xml,
@output_vsd xml
SELECT @source_vsd = N'<vd:vetDocument xmlns:vd="http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2">
<vd:certifiedConsignment xmlns:vd="http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2">
<vd:consignor xmlns:vd="http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2">
<dt:businessEntity xmlns:dt="http://api.vetrf.ru/schema/cdm/dictionary/v2">
<bs:uuid xmlns:bs="http://api.vetrf.ru/schema/cdm/base">04ceb142-053d-11e1-99b4-d8d385fbc9e8</bs:uuid>
</dt:businessEntity>
</vd:consignor>
<vd:consignee xmlns:vd="http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2">
<dt:businessEntity xmlns:dt="http://api.vetrf.ru/schema/cdm/dictionary/v2">
<bs:uuid xmlns:bs="http://api.vetrf.ru/schema/cdm/base">cbee869d-5405-4181-a1d8-7e8c8af4597b</bs:uuid>
</dt:businessEntity>
</vd:consignee>
</vd:certifiedConsignment>
</vd:vetDocument>
'
DECLARE @consignee xml,
@consignor xml
DECLARE @t table(output_vsd_xml xml)
;WITH XMLNAMESPACES( 'http://api.vetrf.ru/schema/cdm/mercury/g2b/applications/v2' as merc,
'http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2' as vd,
'http://api.vetrf.ru/schema/cdm/dictionary/v2' as dt,
'http://api.vetrf.ru/schema/cdm/base' as bs)
SELECT
@consignee = T.C.query('./vd:consignee[1]'),
@consignor = T.C.query('./vd:consignor[1]')
FROM @source_vsd.nodes('/vd:vetDocument/vd:certifiedConsignment') T(C)
DECLARE @szLocalTransactionId nvarchar(max),
@szLogin nvarchar(max),
@szDeliveryDate nvarchar(max)
SELECT @szLocalTransactionId = N'q1234',
@szLogin = N'login',
@szDeliveryDate = N'2015-09-28T17:17:00:00';
;WITH XMLNAMESPACES( 'http://api.vetrf.ru/schema/cdm/mercury/g2b/applications/v2' as merc,
'http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2' as vd,
'http://api.vetrf.ru/schema/cdm/dictionary/v2' as dt,
'http://api.vetrf.ru/schema/cdm/base' as bs)
SELECT @output_vsd = (
SELECT @szLocalTransactionId as 'merc:localTransactionId',
(SELECT @szLogin as 'vd:login' FOR XML PATH ('merc:initiator'), ELEMENTS, TYPE),
(SELECT @szDeliveryDate as 'vd:deliveryDate' FOR XML PATH ('merc:delivery'), ELEMENTS, TYPE)
FOR XML PATH ('merc:processIncomingConsignmentRequest')
)
SELECT @output_vsd as without_inserted_value
select @consignor as inserted_value
SET @output_vsd.modify('
declare namespace merc="http=api.vetrf.ru/schema/cdm/mercury/g2b/applications/v2";
declare namespace vd="http=api.vetrf.ru/schema/cdm/mercury/vet-document/v2";
declare namespace dt="http=api.vetrf.ru/schema/cdm/dictionary/v2";
declare namespace bs="http=api.vetrf.ru/schema/cdm/base";
insert sql:variable("@consignor")
into (/merc:processIncomingConsignmentRequest/merc:delivery/vd:deliveryDate)[1]')
SELECT @output_vsd as with_inserted_value
显然,更新XML变量存在一些问题,因为一旦您将价值放入表中(并将命名空间移至with namespaces()
部分((一切都开始按所谓的开始工作到:
insert into @t (output_vsd_xml)
values (@output_vsd);
WITH XMLNAMESPACES (
'http://api.vetrf.ru/schema/cdm/mercury/g2b/applications/v2' as merc,
'http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2' as vd,
'http://api.vetrf.ru/schema/cdm/dictionary/v2' as dt,
'http://api.vetrf.ru/schema/cdm/base' as bs
)
update t set output_vsd_xml.modify('
insert sql:variable("@consignor")
as first into /merc:processIncomingConsignmentRequest[1]/merc:delivery[1]/vd:deliveryDate[1]'
)
from @t t;
select * from @t;
该表已经在您的代码中,只有未使用:(
遗憾的是,您没有陈述您的预期输出,因此我的答案需要对我的魔术结晶球的一些请求。我很确定,这可以更容易解决:
DECLARE @output_vsd XML;
DECLARE @source_vsd XML =
N'<vd:vetDocument xmlns:vd="http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2">
<vd:certifiedConsignment xmlns:vd="http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2">
<vd:consignor xmlns:vd="http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2">
<dt:businessEntity xmlns:dt="http://api.vetrf.ru/schema/cdm/dictionary/v2">
<bs:uuid xmlns:bs="http://api.vetrf.ru/schema/cdm/base">04ceb142-053d-11e1-99b4-d8d385fbc9e8</bs:uuid>
</dt:businessEntity>
</vd:consignor>
<vd:consignee xmlns:vd="http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2">
<dt:businessEntity xmlns:dt="http://api.vetrf.ru/schema/cdm/dictionary/v2">
<bs:uuid xmlns:bs="http://api.vetrf.ru/schema/cdm/base">cbee869d-5405-4181-a1d8-7e8c8af4597b</bs:uuid>
</dt:businessEntity>
</vd:consignee>
</vd:certifiedConsignment>
</vd:vetDocument>';
- easy-cheesy ,我将命名空间通配符与深search 一起使用 //
DECLARE @consignor xml = @source_vsd.query(N'//*:consignor[1]');
DECLARE @szLocalTransactionId nvarchar(max)= N'q1234';
DECLARE @szLogin nvarchar(max) = N'login';
DECLARE @szDeliveryDate nvarchar(max) = N'2015-09-28T17:17:00:00';
- 您可以将XML直接添加到XML的一代中。不需要表或对.modify()
的任何调用 - 至少我认为是这样。不需要子选择,只是为了增加更深的筑巢。这可以使用像别名这样的XPath
来完成。否则,您会反复获得命名空间声明(不是错误的,但很烦人!(
如果您需要包括命名空间dt
和bs
,只需添加它们即可。但是它们在这里没有使用...
;WITH XMLNAMESPACES( 'http://api.vetrf.ru/schema/cdm/mercury/g2b/applications/v2' as merc,
'http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2' as vd)
SELECT @output_vsd =
(
SELECT @szLocalTransactionId as [merc:localTransactionId],
@szLogin as [merc:initiator/vd:login],
@szDeliveryDate as [merc:delivery/vd:deliveryDate],
@consignor as [merc:delivery/*]
FOR XML PATH ('merc:processIncomingConsignmentRequest')
);
SELECT @output_vsd;
我不知道在哪里放置货物。这是我的输出:
<merc:processIncomingConsignmentRequest xmlns:vd="http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2" xmlns:merc="http://api.vetrf.ru/schema/cdm/mercury/g2b/applications/v2">
<merc:localTransactionId>q1234</merc:localTransactionId>
<merc:initiator>
<vd:login>login</vd:login>
</merc:initiator>
<merc:delivery>
<vd:deliveryDate>2015-09-28T17:17:00:00</vd:deliveryDate>
<vd:consignor xmlns:vd="http://api.vetrf.ru/schema/cdm/mercury/vet-document/v2">
<dt:businessEntity xmlns:dt="http://api.vetrf.ru/schema/cdm/dictionary/v2">
<bs:uuid xmlns:bs="http://api.vetrf.ru/schema/cdm/base">04ceb142-053d-11e1-99b4-d8d385fbc9e8</bs:uuid>
</dt:businessEntity>
</vd:consignor>
</merc:delivery>
</merc:processIncomingConsignmentRequest>