How is fastest way to construct an XML node then a bunch of slow xquery
modify statements?

This SQL Server 2005 logic is rather slow to simply add a new item to an xml
document. Any way to do this all in one statement and faster for creating a
new node without having to re-select to set the attribute values?

set @currentItems.modify('insert <Item></Item> as first into
(/Items/ParentItem[(@id=sql:variable("@parentItemID"))])[1]')
set @currentItems.modify('insert (attribute id {sql:variable("@itemID")},
attribute length {sql:variable("@metadataLength")}, attribute activityDate
{sql:variable("@activityDate_xml")}) into
(/Items/ParentItem[(@id=sql:variable("@parentItemID"))]/Item)[1]')
set @currentItems.modify('replace value of
(/Items/ParentItem[(@id=sql:variable("@parentItemID"))]/@totalCount)[1] with
((/Items/ParentItem[(@id=sql:variable("@parentItemID"))]/@totalCount)[1] +
1)')
set @currentItems.modify('replace value of
(/Items/ParentItem[(@id=sql:variable("@parentItemID"))]/@lastModified)[1]
with sql:variable("@lastModified_xml")')


One idea is to do this in a CLR function that uses a simple string builder
to create the node, but then it has to be reserialized and there is CLR
overhead. Any ideas on how to make this faster?

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is n
Creating a new Access database file (.mdb) in .NET/C#
How much of the functionality of SQL Server is mirrored in ADO.Net
Inconsistent dat paremeter in query
web service for accessing db?