Insert xml into another xml using t-sql

Question

There is received xml file from which I get the frame of consignor. Then I generate an answer xml where I would like to use some block from source xml. That is why I try to insert "consignor" variable which is instance of xml into another xml instance. There is no error, however, it doesn't insert the value... What can be wrong?

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

Is this question solved? Do you need further help? – Shnugo Feb 28 '18 at 12:14 — Shnugo, Feb 28 '18 at 12:14

score 0 · Answer 1 · answered Feb 21 '18 at 12:39

Apparently, there is some issue with update of a XML variable, because as soon as you put your value into the table (and move namespaces into with namespaces() section) everything starts to work as it's supposed to:

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;

The table was already in your code, only it was unused :)

Shnugo · Answer 2 · 2018-02-22T10:15:10.353

Regrettfully you did not state your expected output, so my answer needed some requests to my magic crystall ball. I'm quite sure, this can be solved a bit easier:

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, I use a namespace wildcard together with the deep search with //

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

--You can add your XML directly within the XML's generation. No need for a table or any call to .modify() - at least I assume so. No need for sub-selects, just to add deeper nesting. This can be done with an XPath like alias. Otherwise you'd get your namespace declarations repeatedly (not wrong but very annoying!).

If you need to include namespaces dt and bs just add them. But they are not used here...

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

I did not know, where to place the consignor. This is my output:

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

Insert xml into another xml using t-sql

2 Answers2