472,791 Members | 1,232 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,791 software developers and data experts.

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 3901

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

Similar topics

by: Ken Varn | last post by:
Sometimes when I try to close my managed C++ application, the following dialog displays in Win 2000 Pro: The title of the dialog is "Server Busy". The message is "This action cannot be completed...
by: DarkSpy | last post by:
many c++ compilers including "gcc" have not implemented the "export" keyword, but the comeau compilers made it (just i knew). i want to know about: is it too difficult to implement "export"...
by: Markus Elfring | last post by:
How much are you interested that the attribute "style" will offer the same or similar capabilities like they are provided by the element "style" in the element "head"? Do you want that the...
by: John Baker | last post by:
HI; I feel like a fool..I put CUTE FTP in my last request for help --it should have been CUTE PDF! I have FTP on my mind because I have been working on a web based application, and somehow my...
by: Gomaw Beoyr | last post by:
Two question about the "partial classes" (in the next wersion of ..NET). Question 1 ========== Will partial classes (in the next version of C#) have to be declared "partial" in ALL places. ...
by: John Smith | last post by:
I have a project that consists of about a dozen translation units. I use a command line compiler and it occured to me that I could simplify compiling the project by #include(ing) them in a header...
by: eman1000 | last post by:
I was recently looking at the prototype library (http://prototype.conio.net/) and I noticed the author used the following syntax: Object.extend(MyObj.prototype, { my_meth1: function(){},...
by: John Nagle | last post by:
Here's a wierd problem: I have a little test case for M2Crypto, which just opens up SSL connections to web servers and reads their certificates. This works fine. But if I execute ...
by: Elvis123 | last post by:
I want to "merge" the results of two queries: select DATE,CUSTOMER_ID from DB1 select DATE,CUSTOMER_ID from DB2 Then I want to be able to count the number of customers for each date in the...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.