Figuring it all out: Replacing an XML node with a different node using XmlNode.ReplaceChild and .ImportNode

I'm working on a project that needed to do a seemingly simple thing: based on a parent node element, do a SQL query (results as xml) and then replace the result set into the existing message.

In other words, I needed to update a message with xml from a database call result.

The original xml message had the structure, but not the data. I thought it would be inefficient to loop through all nodes.

My findings on how to do this were slightly ambiguous, and most examples I looked and put into my code usually resulted in the infamous "The node to be removed is not a child of this node" error or some other ambiguous object error message.

The XmlNode.ReplaceChild(new child, old child) assumes that both child nodes are from the same document (I think - maybe the experts out there can input on this). In my case, this wasn't true, since the "new child" was a result from a SQL query.

Here's the original message, in simplified terms:

<internal>
  <routing>
    <step>
    </step>
  </routing>
</internal>

My SQL query resulted in:

<routing>
  <step>
    <sequence>1</sequence>
    <time>12:00</time>
  </step>
  <step>
    <sequence>2</sequence>
    <time>12:30</time>
  </step>
</routing>

And I needed to get to:

<internal>
  <routing>
    <step>
      <sequence>1</sequence>
      <time>12:00</time>
    </step>
    <step>
      <sequence>2</sequence>
      <time>12:30</time>
    </step>
  </routing>
</internal>

The code to complete this isn't exactly straightforward, but the important code is short. Here's how I did it:

// load XML from file and assign xPath location for node

XmlDocument docOriginal = new XmlDocument();

docOriginal.Load("C:\\test.xml");

string strNode = "/request/internal/routing";

// now get SQL from stored procedure

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@ID", strTPID));

cmd.Parameters.Add(new SqlParameter("@Process", strTPP));

conn.Open();

// load returned XML SQL into XmlDocument

XmlDocument docSQL = new XmlDocument();

using (XmlReader reader = cmd.ExecuteXmlReader())
{
docSQL.Load(reader);
}

The next three lines of code are what actually do the magic:

XmlNode oldNode = docOriginal.SelectSingleNode(strNode);
XmlNode newNode = docOriginal.ImportNode(docSQL.DocumentElement, true);
oldNode.ParentNode.ReplaceChild(newNode, oldNode);
return docOriginal;

There's a little bit going on here to fully understand and appreciate this:

1. The first line explains itself – get the node that I want out of the xml document I loaded from file.
   
2. This piece initially was a little hard for me to comprehend, which was the crux of my frustration. The .ReplaceChild in the next line of code seemingly must have the child nodes from the same xmlDocument. Therefore I could not just take any xml and slap it in. The .ImportNode command assigns the SQL result to a new node via docOriginal. So effectively we are saying: take the SQL result and create a new node based on the docOriginal XmlDocument.
3. Step 3 is now self-explanitory and now you don't get those darn error messages.