470,566 Members | 1,689 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,566 developers. It's quick & easy.

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

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
  2.     ID NVARCHAR(20) NOT NULL,
  3.     Dat XML NULL,
  5. )
and I have some records:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO ExampleXML (ID,Dat) VALUES ('1', '<info FavoriteColor="green" FavoriteWebsite="thescripts" Sex="Unknown" />')
  2. INSERT INTO ExampleXML (ID,Dat) VALUES ('2', '<info FavoriteColor="red" FavoriteWebsite="thescripts" Sex="F" />')
  3. INSERT INTO ExampleXML (ID,Dat) VALUES ('3', '<info FavoriteColor="red" FavoriteWebsite="thescripts" FavoriteSandwich="PB n J" Sex="F" />')
  4. 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":
Expand|Select|Wrap|Line Numbers
  2.     @VarID NVARCHAR(20),
  3.     @UpdateXML XML
  4. AS
  5. BEGIN
  6. DECLARE @sqlCommands XML
  7. SELECT @sqlCommands =  
  8.     @UpdateXML.query('
  9.         <commands>
  10.         {
  11.             for $X in /info/@*
  12.                 return <command>{concat("UPDATE ExampleXML SET Dat.modify(&apos;delete /info/@", local-name($X), "&apos;) WHERE ID=&apos;", sql:variable("@VarID"),"&apos;")}</command>
  13.         }
  14.         </commands>
  15.     ')
  17. PRINT CONVERT (NVARCHAR(2000), @sqlCommands)
  19. --shred the <commands><command>... into a result/cursor
  21. SELECT
  22.     CAST(CommandTable.CommandColumn.query('text()') AS NVARCHAR(2000))
  23.     FROM
  24.     @sqlCommands.nodes('commands/command') AS CommandTable(CommandColumn);
  25. OPEN stepper
  27. --EXEC the commands from the result/cursor
  28. DECLARE @cmd NVARCHAR(2000)
  29. FETCH NEXT FROM stepper INTO @cmd
  31.     --DEBUGGING
  32.     PRINT 'cmd: ' + @cmd
  33.     EXEC (@cmd)
  34.     FETCH NEXT FROM stepper INTO @cmd
  35. END
  36. CLOSE stepper
  37. DEALLOCATE stepper
  39. --Build commands to insert given attributes
  40. SELECT @sqlCommands =  
  41.     @UpdateXML.query('
  42.         <commands>
  43.         {
  44.             for $X in /info/@*
  45.                 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>
  46.         }
  47.         </commands>
  48.     ')
  50. PRINT CONVERT (NVARCHAR(2000), @sqlCommands)
  52. --shred the <commands><command>... into a result/cursor
  54. SELECT
  55.     CAST(CommandTable.CommandColumn.query('text()') AS NVARCHAR(2000))
  56.     FROM
  57.     @sqlCommands.nodes('commands/command') AS CommandTable(CommandColumn);
  58. OPEN stepper
  60. --EXEC the commands from the result/cursor
  61. FETCH NEXT FROM stepper INTO @cmd
  63.     --DEBUGGING
  64.     PRINT 'cmd: ' + @cmd
  65.     EXEC (@cmd)
  66.     FETCH NEXT FROM stepper INTO @cmd
  67. END
  68. CLOSE stepper
  69. DEALLOCATE stepper
  71. END
Now, if I execute my stored procedure:
Expand|Select|Wrap|Line Numbers
  1. EXEC MergeXML '1', '<info FavoritePet="dog" Sex="Male" />'
Indeed it will merge "FavoritePet=dog" into ID=1 as well as change "Sex=Male" instead of "Unknown" and it leaves the existing "FavoriteColor" and "FavoriteWebsite" 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:
Expand|Select|Wrap|Line Numbers
  1. <info>
  2. <FavoriteColor>red</FavoriteColor>
  3. <Sex>Male</Sex>
  4. </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.
Aug 9 '07 #1
0 3783

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by DarkSpy | last post: by
52 posts views Thread by Markus Elfring | last post: by
9 posts views Thread by Gomaw Beoyr | last post: by
4 posts views Thread by John Smith | last post: by
13 posts views Thread by eman1000 | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.