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