Some boring XML in SQL 2005 code that some might find useful.
Posted by 2000mph on January 3, 2008
OK at work I had to do some work to manipulate some XML stored in SQL 2005 database using SQL scripts. I have never done that before and I’m only basic level SQL programmer. So I did a lot of searching online and found some useful code bits here and there to get the work done. So as a help to other people but mainly as a reminder for me here are a few of the good bits ofSQL code I found.
How to insert new node into some XML stored in SQL database table.
UPDATE tablename SET fieldname.modify('insert if (/ROOT/nodename/[@attributename="attributevalue"]) then <newnodename newattribute="value"> else () as last into /ROOT[1]')
This uses the XML Modify method and contains an if statement that will only insert the new node if an existing node with a certain attribute value exists in the XML. Also you can only insert one new node in this statement, if you try to add multiple nodes in the same statement it will fail.
The if statement in the code above is only checking for one value, what I wanted to do was check for a XML node that had multiple attributes and then insert a new node next to that. To do that took so searching for the correct syntax but it is possible using a if and statement as shown below.
UPDATE tablename SET fieldname.modify('insert if (/ROOT/nodename/[@attributename1="attributevalue1"] and /ROOT/nodename/[@attributename2="attributevalue2"]) then <newnodename newattribute="value"> else () as last into /ROOT[1]')
You can also use and operator inside a XML Query such and not just an if statement as shown above. Below is some code I used to delete a node matching a XML Query that finds a node with multiple attributes with a set value.
UPDATE tablename SET fieldname.modify('delete (/ROOT/nodename/[@attributename1="attributevalue1" and @attributename2="attributevalue2"])')
If the above code isn’t much use check out the sites below where I got the information I needed to put together this code and everything else I needed to know about SQL XML to complete my work.
http://msdn2.microsoft.com/en-us/library/ms175466.aspx
http://technet.microsoft.com/en-us/library/ms345122.aspx
http://www.15seconds.com/issue/050803.htm
Have Fun
MPH.
