Insertar Multiples Registros Usando XML en .Net/SQL Server

Hay 3 formas principalmente para hacer parse de muchos registros como parámetro a un procedimiento almacenado.

• Valores separados por coma: Es bueno en una escenario donde necesitas pasar múltiples valores pero para una sola columna.
• Documento XML: Necesitamos SQL Server 2005 o superior y tener múltiples valores de columnas.
• Parámetro Tipo Tabla: Necesitamos SQL Server 2008 o superior, También si no nos importa hacer parse en el back-end. Ofrece mejor rendimiento.

Para saber que es un parámetro tipo tabla, como utilizarlo y como realizar acciones de actualización, eliminación, mezcla, etc. Podemos encontrar información acá.

Primero veremos como podemos generar un documento XML en .Net usando la clase XMLSerializer.

Bueno empecemos, digamos que tenemos una lista de personas como la que veremos a continuación y queremos convertirla en XML: 

List<Person> gente = new List<Person> {

     new Person { FirstName = "Esclavo", LastName = "De Luda", Age = 24},

     new Person { FirstName = "Luda", LastName = "Montero", Age = 25 },

     new Person { FirstName = "Mario", LastName = "De Luda", Age = 25 }

};

string strXML = SerializeObject<List<Person>>(gente);




La función para serializar la lista esta acá:




public string SerializeObject<T>(T Obj)

{

    string strxml = string.Empty;

    using (StringWriter sw = new StringWriter())

    {

        XmlSerializer xs = new XmlSerializer(typeof(T));

        xs.Serialize(sw, Obj);

        strxml = sw.ToString();

    }

    return strxml;

}

El resultado de esto es el siguiente:

<?xml version="1.0" encoding="utf-16"?>

<ArrayOfPerson xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<Person>

<FirstName>Esclavo</FirstName>

<LastName>De Luda</LastName>

<Age>24</Age>

</Person>

<Person>

<FirstName>Luda</FirstName>

<LastName>Montero</LastName>

<Age>25</Age>

</Person>

<Person>

<FirstName>Mario</FirstName>

<LastName>De Luda</LastName>

<Age>25</Age>

</Person>

</ArrayOfPerson>




El XML generado tiene una codificación utf-16. Ahora veremos como pasarlo como parámetro a nuestro procedimiento almacenado.




string connectionString = Convert.ToString(ConfigurationManager.ConnectionStrings["XMLDBConnString"]);         

using (SqlConnection cn = new SqlConnection(connectionString))

{

    using (SqlCommand cm = new SqlCommand("GuardarUsuario", cn))

    {

        cm.CommandType = CommandType.StoredProcedure;

        cm.Parameters.Add("@UsersXml", SqlDbType.NVarChar, -1).Value = strXML;

         

        cn.Open();

         

        cm.ExecuteNonQuery();

    }

}




Como se puede observar, el tipo de dato Nvarchar, si necesitas dar soporte a caracteres Unicode. En caso de no necesitar dar soporte a caracteres unicode podemos utilizar esta clase para cambiar el encoding de la siguiente manera:

public class Utf8StringWriter : StringWriter

{

    public override Encoding Encoding

    {

        get { return Encoding.UTF8; }

    }

}




Importante: Si pasas el XML así como esta a un parámetro de tipo de dato varchar provocará el error: Unable to switch the encoding.

Para evadir este error necesitamos simplemente usar la clase Utf8StringWriter descrita anteriormente en lugar de StringWriter y en lugar de Nvarchar usa Varchar.

Ahora para poder hacer parse necesitamos utilizar XQuery. Si no estas familiarizado con este tema te sugiero darte una vuelta por acá o aquí.

Ahora veremos el código de ejemplo de del procedimiento almacenado.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[GuardarUsuario]

    @UsersXml AS NVARCHAR(MAX)

AS

BEGIN

    DECLARE @XML AS XML

        

    SELECT @XML = @UsersXml

     

    INSERT INTO Person (FirstName, LastName, Age)     

    select M.Item.value('FirstName[1]','VARCHAR(50)'),

    M.Item.value('LastName[1]','Varchar(50)'),

    M.Item.value('Age[1]','INT')

    FROM @xml.nodes('/ArrayOfPerson/Person') AS M(Item)

END




Importante: XQuery es sencible a mayusculas, por lo tanto si necesitas una salida diferente puedes hacer adecuaciones al procedimiento.

Básicamente esto es todo lo que necesitas para realizar la inserción, hay varias formas para optimizar el código pero expongo la manera más sencilla de ilustrar el ejemplo.

Importante: A partir de SQL Server 2008, se pueden pasar parámetros tipo tabla a un procedimiento almacenado.

Esto para evitar la sobrecarga de procesamiento del XML en el motor del servidor, más información acá.

Importante: Este articulo es una adaptación libre del How to pass multiple records to stored procedure in Asp.net?de Nilesh Thakkar.

Saludos, José