SQL Server 2005 - Inserting records using OpenXML vs delimited string

Question

I have a bunch of employee names which need to be inserted in a table.

Should I represent my data like this and use OpenXML to insert into the database:-

<Employees>
<Employee>
Emp1
</Employee>
<Employee>
Emp2
</Employee>
<Employee>
Emp2
</Employee>
</Employees>

OR

I should represent the Employee like Emp1,Emp2,Emp3, split the string, add to a table variable and then insert into the database table.

Are there any performance difference between the two approaches. Please note that this is very simple structure without any nesting of employees in the XML and without more than one delimiter in the string. This XML is also not going to be used as schema or anything. Would OpenXML be the overkill? Could anybody give some direction on this?

Answer 1

Using the SQL Server XQuery support, you can easily shred the XML into bits:

INSERT INTO dbo.Employees(EmployeeName)
   SELECT 
      Data.Emp.value('(.)[1]', 'varchar(100)')
   FROM
      @Input.nodes('/Employees/Employee') AS Data(Emp)

The same cannot be said of CSV files - so I would vote for the XML approach.

Answer 2

Well depending on what version of SQL you are using there is a third option that is very easy. If you are using SQL 2008 then you can create a user defined table type.

CREATE TYPE [dbo].[IntegerList] AS TABLE(
    [n] [VARCHAR(100)] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [n] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

I used this with integers but I don't see why you couldn't just change the type to be varchar. Then you use it like this:

CREATE PROCEDURE [dbo].[CheckIds]
    @Ids    IntegerList READONLY
AS
BEGIN
    SELECT *
    FROM [dbo].[Table]  
    WHERE [Id] IN (SELECT n FROM @Ids)
END

Then in your .net code you set it up like so:

int[] Ids = <You id array>
var IdList = new List<SqlDataRecord>();
SqlMetaData[] tvp_definition = { new SqlMetaData("n", SqlDbType.Int) };

foreach (int Id in Ids)
{
    var rec = new SqlDataRecord(tvp_definition);
    rec.SetInt32(0, Id);
    IdList.Add(rec);
}

Then pass it in as param like usual to stored proc call except you make some minor changes:

sqlCommand.Parameters.Add(new SqlParameter { ParameterName = "@Ids", SqlDbType = SqlDbType.Structured, TypeName = "IntegerList", Value = IdList });

Might seem like a lot but it is actually not. It is really clean and no unnecessary code of parsing xml or strings.

I can't remember where I originally found this code. I thought it might have been on this site but when searching I couldn't find it. So if someone finds it I will gladly give credit to whomever it belongs to. Just wanted to share because I was not familiar with this in 2008 and felt it was a very clean approach.