Create this procedure
CREATE PROCEDURE PopulateFullNameInThisXML
@XmlData XML
AS
BEGIN
DECLARE @xmltble TABLE
(
firstName NVARCHAR(MAX),
middleName NVARCHAR(MAX),
lastName NVARCHAR(MAX),
maidenName NVARCHAR(MAX),
suffix NVARCHAR(MAX),
autoLastName NVARCHAR(MAX),
autoFullName NVARCHAR(MAX)
)
INSERT INTO @xmltble
select
R.i.query('firstName').value('.', 'varchar(30)') [firstName],
R.i.query('middleName').value('.', 'varchar(30)') [middleName],
R.i.query('lastName').value('.', 'varchar(30)') [lastName],
R.i.query('maidenName').value('.', 'varchar(30)') [maidenName],
R.i.query('suffix').value('.', 'varchar(30)') [suffix],
R.i.query('autoLastName').value('.', 'varchar(30)') [autoLastName],
R.i.query('autoFullName').value('.', 'varchar(30)') [autoFullName]
from @XmlData.nodes('/Name') R(i)
SELECT
[firstName],
[middleName],
[lastName],
[maidenName],
[suffix],
lastName + suffix AS [autoLastName],
[firstName] + [middleName] + lastName + suffix AS [autoFullName]
FROM @xmltble FOR XML PATH('Name')
END
Now use it as below
DECLARE @XmlData xml
SET @XmlData=' <name>
<firstname>Christophe</firstname>
<middlename>Robert</middlename>
<lastname>Mulford</lastname>
<maidenname />
<suffix>SR</suffix>
<autolastname>Mulford SR</autolastname>
<autofullname></autofullname>
</name>
<name>
<firstname>Christophe</firstname>
<middlename>Robert</middlename>
<lastname>Mulford</lastname>
<maidenname />
<suffix>SR</suffix>
<autolastname>Mulford SR</autolastname>
<autofullname></autofullname>
</name>'
exec PopulateFullNameInThisXML @XmlData
You will get output like this
<Name>
<firstName>Christophe</firstName>
<middleName>Robert</middleName>
<lastName>Mulford</lastName>
<maidenName></maidenName>
<suffix>SR</suffix>
<autoLastName>MulfordSR</autoLastName>
<autoFullName>ChristopheRobertMulfordSR</autoFullName>
</Name>
<Name>
<firstName>Christophe</firstName>
<middleName>Robert</middleName>
<lastName>Mulford</lastName>
<maidenName></maidenName>
<suffix>SR</suffix>
<autoLastName>MulfordSR</autoLastName>
<autoFullName>ChristopheRobertMulfordSR</autoFullName>
</Name>