XQuery in SQL Server to delete XML element
Use of XML is being popular in SQL Server and it comes handy in many situation as I have explained in many of my past Xquery/XML related article. Today I come up with one more example which shows usage of XQuery to delete element from XML data based on the condition you have.
CREATE TABLE Orders ( ID INT IDENTITY(1,1), ClientID VARCHAR(6), TestDetails XML, OrderDate DATETIME DEFAULT GETDATE() ) GO
INSERT INTO Orders (ClientID,TestDetails)
SELECT ‘CHEM02′,'<TestDetails><Test Name=”VOC MS Group1” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘ACCU01′,'<TestDetails><Test Name=”SVOC Full” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘CBI001′,'<TestDetails><Test Name=”PCB” /><Test Name=”Metals” /></TestDetails>’
We can see that we have “TestDetails” as a root element in above given XML along with child elements “Test”. Suppose we have requirement to delete all elements which has test name “Pesticide Group1”, how can we do it with the help of XQuery?
Here is the answer:
--Update with XQuery to Delete element UPDATE Orders SET TestDetails.modify (' delete /TestDetails/Test[@Name cast as xs:string ? = "Pesticide Group1" cast as xs:string ?]') GO -- confirm result by querying Orders Table SELECT * FROM Orders
I have few more article written on the subject of XML/XQuery, if you are interested, have a look at following list:
- WHERE clause on XML column in SQL Server table (Click Here)
- Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
- For XML PATH ROOT in SQL Server 2008 (Click Here)
- Read typed XML in SQL Server 2008 (Click Here)
- Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
- Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
- Read XML node on same level inSQL Server 2008/2005 (Click Here)
- Load Relational XML data in SQL Server 2005 (Click Here)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.