Add child note above node

Am trying to get from SQL to XML however have to format this correctly or it will not work. I am using C# to insert my sql between the QBXML

select employee_name as Name ,'OCI Associates' as [CompanyName],'Mr' as Salutation ,
 LEFT(employee_name,CHARINDEX(' ',employee_name + ' ')-1) as FirstName,  REVERSE(LEFT(REVERSE(employee_name),
CHARINDEX(' ',REVERSE(employee_name))- 1)) AS LastName


  from EMPLOYEELIST 
for xml raw('CustomerAdd') , ROOT('CustomerAddRq'), ELEMENTS

This is what I get

<CustomerAdd>
        <Name>Zohreh FAKELASTNAME</Name>
        <Salutation>Mr</Salutation>
        <FirstName>Zohreh</FirstName>
        <LastName>FAKELASTNAME</LastName>
      </CustomerAdd>
      <CustomerAdd>
        <Name>Phillip FAKELASTNAME</Name>
        <Salutation>Mr</Salutation>
        <FirstName>Phillip</FirstName>
        <LastName>FAKELASTNAME</LastName>
      </CustomerAdd>

This is what I need

”’

 <CustomerAddRq>
<CustomerAdd>
        <Name>Zohreh FAKELASTNAME</Name>
        <Salutation>Mr</Salutation>
        <FirstName>Zohreh</FirstName>
        <LastName>FAKELASTNAME</LastName>
      </CustomerAdd>
 </CustomerAddRq>
 <CustomerAddRq>
  <CustomerAdd>
    <Name>Phillip FAKELASTNAME</Name>
    <Salutation>Mr</Salutation>
    <FirstName>Phillip</FirstName>
    <LastName>FAKELASTNAME</LastName>
  </CustomerAdd>

”’

I am not sure how to add the “CustomerAddRq” over each “CustomerAdd”

Answer

Try xml linq. I took a string array of you elements which I assume was coming from the database and then added to you root node.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string[] xmlArray = {
                                    "<CustomerAdd>" +
                                      "<Name>Zohreh FAKELASTNAME</Name>" +
                                      "<Salutation>Mr</Salutation>" +
                                      "<FirstName>Zohreh</FirstName>" +
                                      "<LastName>FAKELASTNAME</LastName>" +
                                    "</CustomerAdd>",
                                    "<CustomerAdd>" +
                                      "<Name>Phillip FAKELASTNAME</Name>" +
                                      "<Salutation>Mr</Salutation>" +
                                      "<FirstName>Phillip</FirstName>" +
                                      "<LastName>FAKELASTNAME</LastName>" +
                                    "</CustomerAdd>"
                                };
            XElement customerAddRq = new XElement("CustomerAddRq");
            customerAddRq.Add(xmlArray.Select(x => XElement.Parse(x)));
        }
    }
}