Dynamic Insertion Location of XML Node in Existing Instance Using XML-DML

Question

I haven't been able to find any documentation if it's possible to insert an xml node dynamically within an existing xml instance in SQL Server (2012). I know that you can have a conditional value inserted or replaced, but there doesn't seem to be any documentation if the location of insertion can be done dynamically based on some conditions. For example let's say I have this XML-DML call:

SET @xml.modify('insert <SecondaryContact><Name>{ sql:variable("@contactName") }</Name>
                    <Phone>{ sql:variable("@contactPhone") }</Phone>
                    <Email>{ sql:variable("@contactEmail") }</Email></SecondaryContact>
                    after (/Project/PrimaryContact)[1]');

Would a modification where the node listed following the after keyword is conditional be valid syntax? The following is an example of what I'm referring:

SET @xml.modify('insert <TechnicalContact><Name>{ sql:variable("@contactName") }</Name>
                    <Phone>{ sql:variable("@contactPhone") }</Phone>
                    <Email>{ sql:variable("@contactEmail") }</Email></TechnicalContact>
                    after (
                        if(count(/Project/SecondaryContact) = 0)
                        then (/Project/PrimaryContact)[1]
                        else (/Project/SecondaryContact)(1)
                    )');

Is the only way to dynamically chose the location to have IF..ELSE statements outside of the XML DML statements, or is my example XML-DML valid?

EDIT Example XML:

<root>
    ...
    <PrimaryContact Id="1234">
        <Name>John Doe</Name>
        <Phone>555-555-5555</Phone>
        <Email>somewhere@test.com</Email>
    </PrimaryContact>
    <SecondaryContact Id="1236">   <--OPTIONAL
        <Name>John Doe1</Name>
        <Phone>555-555-5556</Phone>
        <Email>somewhere1@test.com</Email>
    </SecondaryContact>
    <TechnicalContact Id="2234"> <--OPTIONAL
        <Name>John Doe2</Name>
        <Phone>555-555-5255</Phone>
        <Email>somewhere3@test.com</Email>
    </TechnicalContact>
    ...
</root>

I'm aware that the structure isn't ideal. It should be <Contacts><Contact Type="PRIMARY" Id="1234">...</Contact>...</Contacts>, but wanted to see if the dynamic insertion location was possible within the DML statement. Cursor usage is OK for this question as it would be for a one-time update.

Please provide an example of your XML for both conditions and the expected output for both cases. — Shnugo, Nov 04 '16 at 14:44
@Shnugo Added an example of the structure. This is more of a theoretical question on if this is possible within a single DML statement — JNYRanger, Nov 04 '16 at 15:23
`.modify()` is very limited... It might be quite easy with a `FLWOR-query()`, something like `UPDATE TABLE xyz SET XmlColumn=XmlColumn.query(N'FLWOR XQuery')`... But this depends on your actual needs... Did you check my answer? — Shnugo, Nov 04 '16 at 15:26
@Shnugo yeah I took a look. I'm changing the XML schema, so this isn't actually necessary, but since I couldn't find any documentation / resources that showed showed if this syntax was even possible I figured I'd ask. Your approach is interesting and eliminates cursors, but can't do an insertion between `` & `` nodes if the secondary didn't exist and is now needed. (I know that I didn't make that clear in the question though!) — JNYRanger, Nov 04 '16 at 15:30
Another approach is: Use a CTE to shred your XML, use `.query()` to get all unaffected nodes, `.value()` to extract affected values. Then use a simple `SELECT ... FOR XML PATH()` statement to rebuild your XML the way you need it... — Shnugo, Nov 04 '16 at 15:46

Ben · Accepted Answer · 2016-11-04T15:52:34.850

This suggestion is not quite the same, as it will insert after whichever one comes later in the document, rather than after SecondaryContact, but I suspect in your case this is the same thing:

SET @xml.modify('insert 
    <TechnicalContact><Name>{ sql:variable("@contactName") }</Name>
    <Phone>{ sql:variable("@contactPhone") }</Phone>
    <Email>{ sql:variable("@contactEmail") }</Email></TechnicalContact>

    after (/Project/*[
        local-name(.) = "SecondaryContact" 
        or local-name(.) = "PrimaryContact" 
    ])[last()]
');

Or:

if @xml.value('count(/Project/SecondaryContact)', 'int') = 0
begin
  SET @xml.modify('insert <TechnicalContact><Name>{ sql:variable("@contactName") }</Name>
                <Phone>{ sql:variable("@contactPhone") }</Phone>
                <Email>{ sql:variable("@contactEmail") }</Email></TechnicalContact>
                after (/Project/PrimaryContact)[1]
                ');
end else begin
  SET @xml.modify('insert <TechnicalContact><Name>{ sql:variable("@contactName") }</Name>
                <Phone>{ sql:variable("@contactPhone") }</Phone>
                <Email>{ sql:variable("@contactEmail") }</Email></TechnicalContact>
                after (/Project/SecondaryContact)[1]
                ');
end

This is what I have currently, but wanted to know if it could be condensed into a single DML statement. — JNYRanger, Nov 04 '16 at 15:16
I like this (+1 from my side), and I admit it was the first idea I had too... But I assumed, that the real world issue is sitting in a table and procedural *multi-statement-sql* would force to use a cursor or some other kind of loop... Might be, that this is straight forward and just perfect :-) — Shnugo, Nov 04 '16 at 15:16
Well you don't have to do a loop you can bung the test into the `WHERE` clause and do two updates? — Ben, Nov 04 '16 at 15:17
Loops aren't necessarily that bad :-) At least they are easy to understand! Not a fan of cursors though, I tend to use a `declare @temp table` variable. — Ben, Nov 04 '16 at 15:19

Shnugo · Answer 2 · 2016-11-04T15:47:00.860

Until you provide some more examples (see my comment) the best I can think of is to create the node to insert externally and insert it as last

But - quite sure - there is a better approach...

DECLARE @contactName NVARCHAR(100)='TestName';
DECLARE @contactPhone NVARCHAR(100)='TestPhone';
DECLARE @contactEmail NVARCHAR(100)='TestEmail';

DECLARE @tbl TABLE(ID INT IDENTITY,Descr VARCHAR(100),XmlColumn XML);
INSERT INTO @tbl VALUES
 ('With secondary'
 ,N'<Project>
<PrimaryContact>test Primary</PrimaryContact>
<SecondaryContact>test Secondary</SecondaryContact>
</Project>')
,('Only primary'
,N'<Project>
<PrimaryContact id="prim">test Primary</PrimaryContact>
</Project>');

UPDATE @tbl SET XmlColumn.modify
(
    N'insert sql:column("x.NodeToInsert") as last into (/Project)[1]'
)
FROM @tbl
CROSS APPLY
(
    SELECT
    (
        SELECT(
                    SELECT @contactName AS [Name]
                          ,@contactPhone AS [Phone]
                          ,@contactEmail AS [Email]
                    WHERE XmlColumn.exist('/Project/SecondaryContact')=0
                    FOR XML PATH('SecondaryContact'),TYPE
               ) AS [node()]
              ,(
                    SELECT @contactName AS [Name]
                          ,@contactPhone AS [Phone]
                          ,@contactEmail AS [Email]
                    WHERE XmlColumn.exist('/Project/SecondaryContact')=1
                    FOR XML PATH('TechnicalContact'),TYPE
               ) AS [node()]
        FOR XML PATH(''),TYPE
    ) AS NodeToInsert
) AS x

SELECT * FROM @tbl

UPDATE

Another approach is: Use a CTE to shred your XML, use .query() to get all unaffected nodes, .value() to extract affected values. Then use a simple SELECT ... FOR XML PATH() statement to rebuild your XML the way you need it...

score 1 · Answer 3 · answered Nov 04 '16 at 18:43

1

You can construct a sequence of SecondaryContact and PrimaryContact in that order and add the node after the first occurrence.

insert 
  <TechnicalContact>
    <Name>{ sql:variable("@contactName") }</Name>
    <Phone>{ sql:variable("@contactPhone") }</Phone>
    <Email>{ sql:variable("@contactEmail") }</Email>
  </TechnicalContact>
after (
      /Project/SecondaryContact,
      /Project/PrimaryContact
      )[1]

answered Nov 04 '16 at 18:43

Mikael Eriksson

136,425
22
210
281

Based on the order that you enter the items in the list following the after keyword it'll select the first that exists per the singleton? I know that SQL DML doesn't implement all of XQuery, so I wasn't sure if this was supported. If so, this is EXACTLY what I was looking for... – JNYRanger Nov 04 '16 at 18:59
@JNYRanger Sure, it will insert after the first element it finds. Now, if you have multiple SecondaryContact elements it will still insert after the first SecondaryContact it finds, not the last one. If you want the TechnicalContact to be inserted after the last Secondary or after the last Primary you should switch the order of the elements in the construct and use [last()] as a final predicate. – Mikael Eriksson Nov 04 '16 at 19:18
The sequence you construct create a new xml that is used for searching. Secondary elements first followed by the Primary elements and then it goes and looks for the first element in that sequence and inserts the Technical element after that element in the original XML. – Mikael Eriksson Nov 04 '16 at 19:20
That's exactly what I was trying to determine how to do. Awesome! – JNYRanger Nov 04 '16 at 20:18

Dynamic Insertion Location of XML Node in Existing Instance Using XML-DML

3 Answers3

UPDATE