Collection to Xml

converttoxml.cs

public static string DataTableToXML(DataTable dt)
{
    var stringwriter = new System.IO.StringWriter();
    var serializer = new XmlSerializer(dt.GetType());
    serializer.Serialize(stringwriter, dt);

    stringwriter.ToString();

    System.IO.StringWriter writer = new System.IO.StringWriter();
    writer = new System.IO.StringWriter();
    dt.WriteXml(writer, XmlWriteMode.IgnoreSchema, true);

    string xml = writer.ToString();

    return xml;
}

public static DataTable ModelDataTable(FormCollection collection, string UserId, string LanguageId, string ModelId)
{
    DataTable dt = new DataTable("DataCollection");

    dt.Columns.Add("AttributeId", typeof(Int32));
    dt.Columns.Add("LanguageId", typeof(Int32));
    dt.Columns.Add("ModelId", typeof(Int32));
    dt.Columns.Add("DataValue", typeof(string));
    dt.Columns.Add("UserId", typeof(string));
    //dt.Columns.Add("TranslatedDateModified", typeof(string));

    foreach (string item in collection)
    {
        if (item != "X-Requested-With")
        {
            int AttributeId = int.Parse(item.Split('-')[1].Split('*')[0]);
            var DataValue = collection[item];
            //string TranslatedDateModified = item.Split('*')[1];

            dt.Rows.Add(AttributeId, int.Parse(LanguageId), int.Parse(ModelId), DataValue, UserId);
        }
    }

    return dt;
}

DataTable dt = ConvertModel.ModelDataTable(collection, UserId, LanguageId, ModelId);
string xml = ConvertModel.DataTableToXML(dt);

sp.sql

DECLARE @xml xml
SET		@xml =
'<DocumentElement>
  <DataCollection>
    <ProductCode>123456</ProductCode>
    <Brand1>Screwfix</Brand1>
    <Brand1GTIN>50073577510048</Brand1GTIN>
    <Brand2>Random</Brand2>
    <Brand2GTIN>50073577599999</Brand2GTIN>
  </DataCollection>
  <DataCollection>
    <ProductCode>1</ProductCode>
    <Brand1>Screwfiz</Brand1>
    <Brand1GTIN>50073577510048</Brand1GTIN>
    <Brand2>Random</Brand2>
    <Brand2GTIN>50073577599999</Brand2GTIN>
  </DataCollection>
</DocumentElement>
';

WITH C AS
(   
	SELECT script.Item.query('./ProductCode').value('.','VARCHAR(100)') ProductCode,
			script.Item.query('./Brand1').value('.','VARCHAR(100)') Brand1,
			script.Item.query('./Brand1GTIN').value('.','VARCHAR(100)') Brand1GTIN,
			script.Item.query('./Brand2').value('.','VARCHAR(100)') Brand2,
			script.Item.query('./Brand2GTIN').value('.','VARCHAR(100)') Brand2GTIN
	FROM @xml.nodes('/DocumentElement/DataCollection') AS script(Item)
)

MERGE [dbo].[ProductList] pl
    USING C 
        ON C.ProductCode = pl.ProductCode --AND
    WHEN MATCHED 
		--AND C.ProductCode = p.ProductCode
        THEN UPDATE
        SET ProductCode = C.ProductCode,
			Brand1 = C.Brand1,
			Brand1GTIN = C.Brand1GTIN,
			Brand2 = C.Brand2,
			Brand2GTIN = C.Brand2GTIN
    WHEN NOT MATCHED THEN
        INSERT (ProductCode, Brand1, Brand1GTIN, Brand2, Brand2GTIN)
        VALUES (C.ProductCode, C.Brand1, C.Brand1GTIN, C.Brand2, C.Brand2GTIN);