使用T-SQL将XML插入另一个XML



有接收到的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来完成。否则,您会反复获得命名空间声明(不是错误的,但很烦人!(

如果您需要包括命名空间dtbs,只需添加它们即可。但是它们在这里没有使用...

;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>

相关内容

  • 没有找到相关文章

最新更新