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>[email protected]</Email>
    </PrimaryContact>
    <SecondaryContact Id="1236">   <--OPTIONAL
        <Name>John Doe1</Name>
        <Phone>555-555-5556</Phone>
        <Email>[email protected]</Email>
    </SecondaryContact>
    <TechnicalContact Id="2234"> <--OPTIONAL
        <Name>John Doe2</Name>
        <Phone>555-555-5255</Phone>
        <Email>[email protected]</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.

Answer 1

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

Answer 2

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

Answer 3

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]