By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,319 Members | 2,361 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,319 IT Pros & Developers. It's quick & easy.

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

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE ExampleXML(
  2.     ID NVARCHAR(20) NOT NULL,
  3.     Dat XML NULL,
  4.  CONSTRAINT PK_ExampleXML PRIMARY KEY (ID ASC)
  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" />')
  5.  
And I made a stored Procedure to do the "merge":
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE MergeXML
  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.     ')
  16. --DEBUGGING
  17. PRINT CONVERT (NVARCHAR(2000), @sqlCommands)
  18.  
  19. --shred the <commands><command>... into a result/cursor
  20. DECLARE stepper CURSOR FORWARD_ONLY READ_ONLY FOR 
  21. SELECT
  22.     CAST(CommandTable.CommandColumn.query('text()') AS NVARCHAR(2000))
  23.     FROM
  24.     @sqlCommands.nodes('commands/command') AS CommandTable(CommandColumn);
  25. OPEN stepper
  26.  
  27. --EXEC the commands from the result/cursor
  28. DECLARE @cmd NVARCHAR(2000)
  29. FETCH NEXT FROM stepper INTO @cmd
  30. WHILE @@FETCH_STATUS = 0 BEGIN
  31.     --DEBUGGING
  32.     PRINT 'cmd: ' + @cmd
  33.     EXEC (@cmd)
  34.     FETCH NEXT FROM stepper INTO @cmd
  35. END
  36. CLOSE stepper
  37. DEALLOCATE stepper
  38.  
  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.     ')
  49. --DEBUGGING
  50. PRINT CONVERT (NVARCHAR(2000), @sqlCommands)
  51.  
  52. --shred the <commands><command>... into a result/cursor
  53. DECLARE stepper CURSOR FORWARD_ONLY READ_ONLY FOR 
  54. SELECT
  55.     CAST(CommandTable.CommandColumn.query('text()') AS NVARCHAR(2000))
  56.     FROM
  57.     @sqlCommands.nodes('commands/command') AS CommandTable(CommandColumn);
  58. OPEN stepper
  59.  
  60. --EXEC the commands from the result/cursor
  61. FETCH NEXT FROM stepper INTO @cmd
  62. WHILE @@FETCH_STATUS = 0 BEGIN
  63.     --DEBUGGING
  64.     PRINT 'cmd: ' + @cmd
  65.     EXEC (@cmd)
  66.     FETCH NEXT FROM stepper INTO @cmd
  67. END
  68. CLOSE stepper
  69. DEALLOCATE stepper
  70.  
  71. END
  72.  
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
Share this question for a faster answer!
Share on Google+

Post your reply

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