sql-serverxmlsql-server-2012sqlxmlxml-dml

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


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.


Solution

  • 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