A puzzle "merging" XML attributes in an XML column with dynamic SQL,XQuery, and XPath

[jeoffh]

Background:
I am trying to "merge" some attributes into an existing XML column in my MS SQL 2005 database. The general idea is that I have an XML column in a table and I would like to update/delete some values while leaving the other values alone. I am designing this database/table/column so maybe I could use attributes or elements/nodes, the choice is ultimately mine. The one constraint is that I have to allow for customized name/value pairs. The pairs will always be strings, but I cannot limit the names or values to any known set (like only allowing "Last Name", "First Name", and "Favorite Pet" because the user may want to keep track of "Favorite Color", etc. in the future).

My Progress:
Say I have a (simplified) table:

CREATE TABLE ExampleXML(
    ID NVARCHAR(20) NOT NULL,
    Dat XML NULL,
 CONSTRAINT PK_ExampleXML PRIMARY KEY (ID ASC)
)

and I have some records:

INSERT INTO ExampleXML (ID,Dat) VALUES ('1', '<info FavoriteColor="green" FavoriteWebsite="thescripts" Sex="Unknown" />')
INSERT INTO ExampleXML (ID,Dat) VALUES ('2', '<info FavoriteColor="red" FavoriteWebsite="thescripts" Sex="F" />')
INSERT INTO ExampleXML (ID,Dat) VALUES ('3', '<info FavoriteColor="red" FavoriteWebsite="thescripts" FavoriteSandwich="PB n J" Sex="F" />')
INSERT INTO ExampleXML (ID,Dat) VALUES ('4', '<info FavoriteWebsite="thescripts" FavoriteSandwich="Ham n Swiss" Sex="F" />')
 

And I made a stored Procedure to do the "merge":

CREATE PROCEDURE MergeXML
    @VarID NVARCHAR(20),
    @UpdateXML XML
AS
BEGIN
DECLARE @sqlCommands XML
SELECT @sqlCommands =  
    @UpdateXML.query('
        <commands>
        {
            for $X in /info/@*
                return <command>{concat("UPDATE ExampleXML SET Dat.modify(&apos;delete /info/@", local-name($X), "&apos;) WHERE ID=&apos;", sql:variable("@VarID"),"&apos;")}</command>
        }
        </commands>
    ')
--DEBUGGING
PRINT CONVERT (NVARCHAR(2000), @sqlCommands)
 
--shred the <commands><command>... into a result/cursor
DECLARE stepper CURSOR FORWARD_ONLY READ_ONLY FOR 
SELECT
    CAST(CommandTable.CommandColumn.query('text()') AS NVARCHAR(2000))
    FROM
    @sqlCommands.nodes('commands/command') AS CommandTable(CommandColumn);
OPEN stepper
 
--EXEC the commands from the result/cursor
DECLARE @cmd NVARCHAR(2000)
FETCH NEXT FROM stepper INTO @cmd
WHILE @@FETCH_STATUS = 0 BEGIN
    --DEBUGGING
    PRINT 'cmd: ' + @cmd
    EXEC (@cmd)
    FETCH NEXT FROM stepper INTO @cmd
END
CLOSE stepper
DEALLOCATE stepper
 
--Build commands to insert given attributes
SELECT @sqlCommands =  
    @UpdateXML.query('
        <commands>
        {
            for $X in /info/@*
                return <command>{concat("UPDATE ExampleXML SET Dat.modify(&apos;insert (attribute ",local-name($X),"{&quot;",data($X),"&quot;}) into (/info[1])&apos;) WHERE ID=&apos;", sql:variable("@VarID"), "&apos;")}</command>
        }
        </commands>
    ')
--DEBUGGING
PRINT CONVERT (NVARCHAR(2000), @sqlCommands)
 
--shred the <commands><command>... into a result/cursor
DECLARE stepper CURSOR FORWARD_ONLY READ_ONLY FOR 
SELECT
    CAST(CommandTable.CommandColumn.query('text()') AS NVARCHAR(2000))
    FROM
    @sqlCommands.nodes('commands/command') AS CommandTable(CommandColumn);
OPEN stepper
 
--EXEC the commands from the result/cursor
FETCH NEXT FROM stepper INTO @cmd
WHILE @@FETCH_STATUS = 0 BEGIN
    --DEBUGGING
    PRINT 'cmd: ' + @cmd
    EXEC (@cmd)
    FETCH NEXT FROM stepper INTO @cmd
END
CLOSE stepper
DEALLOCATE stepper
 
END
 

Now, if I execute my stored procedure:

EXEC MergeXML '1', '<info FavoritePet="dog" Sex="Male" />'

Indeed it will merge "FavoritePet=do g" into ID=1 as well as change "Sex=Male" instead of "Unknown" and it leaves the existing "FavoriteCo lor" and "FavoriteWebsit e" attributes alone.

My question is:
Is there a better/faster way to do this? 100+ lines of a stored procedure to do this just seems awfully extreme to me. I am so new to XQuery and XPath et al. that I am very dangerous and I have a feeling that there should be a much more elegant solution but I cannot find anything on the Web.

As I stated, I am designing this database to handle user-selectable (i.e. unknown) fields - things like "Favorite Sausage" could be added in the future or who knows what. I am not stuck on using XML attributes, maybe I should use XML elements instead like:

<info>
<FavoriteColor>red</FavoriteColor>
<Sex>Male</Sex>
</info>

I am also considering a three-column table (ID, FieldName, FieldValue) but I am trying to get this XML idea to work. Given that I am so new at this, maybe I am trying to fit a square peg (the feature of supporting unknown future variables) into a round hole (using XML and attributes or elements in SQL Server 2005). I can still use a rational-database “three-column” method for storage (one record/row for each user-supplied parameter and keyed from the same ID) but pass the parameters via XML. I am guessing that this would make the stored procedure much smaller and probably faster but I am not sure how much faster.