Is using the XML datatype as a DataTable acceptable?

Question

For some reason I feel like I'm going about this the painful way. I have an INSERT xml list. I'm using XML because I need to pass in more than one dynamic value. Since we're saving the event, I already have that ID. What I won't know is the Group or Room ID, so I'm using XML to essentially pass in a datatable.

Here is an example INSERT portion:

IF @eventGroupTagRoomListInsert IS NOT NULL
    BEGIN
        INSERT INTO EventGroupTagRoomLink(EventID, RoomID, GroupTagID, IsDeleted, LastModifiedBy, SessionID)
            SELECT
                @eventID AS EventID,
                ParamValues.Id.query('RoomId').value('.', 'int') AS RoomID,
                ParamValues.Id.query('GroupTagID').value('.', 'int') AS GroupTagID,
                0 AS IsDeleted,
                @lastModifiedBy  AS LastModifiedBy,
                @sessionId AS SessionID
            FROM @eventGroupTagRoomListInsert.nodes('/Entities/entity') AS ParamValues(ID)
        IF @@ERROR <> 0 GOTO ERR_HANDLER
    END

Is there cleaner way to do this?

Answer 1

I'd say yes, is acceptable.

In theory in SQL 2008 you have Table Valued Parameters which may be simpler, as syntax at least:

INSERT INTO EventGroupTagRoomLink
  (EventID, RoomID, GroupTagID, IsDeleted, LastModifiedBy, SessionID) 
SELECT EventID, RoomID, GroupTagID, IsDeleted, LastModifiedBy, SessionID 
FROM @eventGroupTagRoomTVP

But between backward compatibility issues (does not work on 2005) and the rather unfriendly manner to present the TVP to ADO.Net (must be a datatable or DbDataReader, will not accept for example a linq query expression) I'm not sure I'd move to TVP over XML.

Answer 2

No problems with this approach.

I use it in several places because it's an effective wayx (especially in SQL Server 2005+) to deal with arrays and lists

Erland and his famous "Arrays and Lists" article

If you read it and the tests too, XML is usually the fastest "standalone" (no CLR needed) method