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:
Expand|Select|Wrap|Line Numbers
- CREATE TABLE ExampleXML(
- ID NVARCHAR(20) NOT NULL,
- Dat XML NULL,
- CONSTRAINT PK_ExampleXML PRIMARY KEY (ID ASC)
- )
Expand|Select|Wrap|Line Numbers
- 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" />')
Expand|Select|Wrap|Line Numbers
- 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('delete /info/@", local-name($X), "') WHERE ID='", sql:variable("@VarID"),"'")}</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('insert (attribute ",local-name($X),"{"",data($X),""}) into (/info[1])') WHERE ID='", sql:variable("@VarID"), "'")}</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
Expand|Select|Wrap|Line Numbers
- EXEC MergeXML '1', '<info FavoritePet="dog" Sex="Male" />'
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:
Expand|Select|Wrap|Line Numbers
- <info>
- <FavoriteColor>red</FavoriteColor>
- <Sex>Male</Sex>
- </info>