Delete operations over SQL XML should be possible with either erasing the XML document with the XML data type column or delete XML tag or attribute in the XML document using XQuery. The utilization of the XML data type with XQuery is tricky and significant in SQL Server.
Delete SQL XML document activity would be the requirement of replacing blank XML with existing one XML document or delete a row with applying a filter on the table or delete XML tag in the XML data type column with the help of XQuery. Each approach is a requirement-based use case in the development. For Example, a platform is utilizing one of the known requirements, “Dynamic client Form” of the Software Industry. As common user activity on the platform, when the client Reset the details of the particular form, one of these activities need to a .
In this article, we will show you how to delete XML nodes from a SQL XML Document in different ways. Here is the sample XML Document to demonstrate various XML delete tag operation.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
<ns:Resume xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"> <ns:Name> <ns:Name.Prefix>Mr.</ns:Name.Prefix> <ns:Name.First>Max</ns:Name.First> <ns:Name.Middle></ns:Name.Middle> <ns:Name.Last>Benson</ns:Name.Last> <ns:Name.Suffix></ns:Name.Suffix> </ns:Name> <ns:Employment> <ns:Emp.StartDate>1994-06-15Z</ns:Emp.StartDate> <ns:Emp.OrgName>Wingtip Toys</ns:Emp.OrgName> <ns:Emp.JobTitle>Production Manager</ns:Emp.JobTitle> <ns:Emp.Responsibility>Production manager Quality assurance lead Safety representative ISO 9000 certification lead Production line worker </ns:Emp.Responsibility> <ns:Emp.FunctionCategory>Production</ns:Emp.FunctionCategory> <ns:Emp.IndustryCategory>Manufacturing</ns:Emp.IndustryCategory> <ns:Emp.Location> <ns:Location> <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion> <ns:Loc.State>FL </ns:Loc.State> <ns:Loc.City>Orlando</ns:Loc.City> </ns:Location> </ns:Emp.Location> </ns:Employment> </ns:Resume> |
This SQL XML with client information will be related to each row in the table HumanResources.
Delete the value in the XML tag and Delete the XML tag in the XML data type column, both are altogether different things. We will show an example with sample data as below.
Delete XML Tag or Node in the SQL XML document
When a user needed to delete the XML tag or node in the document, then this below sample can be useful. Using the “.modify” function will delete the XML tag utilizing the target node path with the index value in the XQuery. Parameters of the XQuery are, what conditions do you have to meet to remove a particular node? What rules would you be following to delete the particular nodes?
|
1 |
SET @XMl_doc.modify ('delete (/*:Resume/*:Name/*:Name.Last)[1]') |
SQL XML after Query Execution:
|
1 2 3 4 5 6 7 8 |
<ns:Resume xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"> <ns:Name> <ns:Name.Prefix>Mr.</ns:Name.Prefix> <ns:Name.First>Max</ns:Name.First> <ns:Name.Middle /> <ns:Name.Suffix /> </ns:Name> … |
Here, the above sample query is deleting the XML tag, which is existing on a derived path in the .modify function with delete keyword. The index of the Element tag is referenced to action on target SQL XML tag with the XML path in the document. Therefore, Target XML node should be defined with the accurate element index in the XQuery to delete the exact SQL XML tag. Element id in bracket [] represents that the order position of the XML tag in the XPATH. It helps when multiple tags are existing with the same XML tag name. When a user does not mention the element index with the XML node in XQuery, then multiple XML tags can be deleted, which exist on the derived XPATH in the SQL XML.
Delete XML Tag value in XML data type document
When a client needed to remove just value which exists in the particular XML tag or node in the SQL XML document and not to remove the tag, then this underneath sample can be helpful. XML Element value will be erased utilizing the text() with the delete keyword in modify() function.
|
1 |
SET @XMl_doc.modify ('delete (/*:Resume/*:Name/*:Name.Last/text())') |
|
1 2 3 4 5 6 7 8 9 |
<ns:Resume xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"> <ns:Name> <ns:Name.Prefix>Mr.</ns:Name.Prefix> <ns:Name.First>Max</ns:Name.First> <ns:Name.Middle /> <ns:Name.Last /> <ns:Name.Suffix /> </ns:Name> … |
Here, the above example XQuery is to delete the value of Name.Last element in the SQL XML. A query is deleting just value, which exists in the referenced path in .modify function. So after deleting the value using delete in the modify() function, blank tag <ns:Name.Last /> will exist in the XML document.
In this above sample, a namespace is used with each XML tag in the XML data type column. A namespace is a major subject itself in the XML with SQL Server. To deal with the namespace in the XQuery, little query syntax will be changed. /*: should be added before every node in the XML path in any XML Query.
If a namespace is not defined or does not exist in the XML document, then the XML path will be used with / to separate the XML tags or nodes. For instance, the below XQuery illustration is equivalent to the first example to delete the XML node in the XML document.
|
1 |
SET @XMl_doc.modify ('delete (/Resume/Name/Name.Last)[1]') |
Here, we used only / in the XML path /Resume/Name/Name.Last.
DELETE SQL XML element and element value – both ways are being used in the different case requirements and nature of the data. When a user is deleting the field in the XML data type document, then the above XQuery example is useful. When a SQL XML tag value is deleted and again the user wants to insert a new value in that XML tag, then XML needs that blank node in the XML document to fill it once again. In that case, the second alternative, delete in .modify with text(), would be helpful within XQuery.
Delete XML document in the SQL Server table
In such cases, the requirement can be to delete the existing XML document from the row of the table. What can be an alternate way and use case?
- Replace a blank XML template with the existing XML document
- Delete a row in the table
- Keep blank value on the XML column for the row in the row
Replace blank XML template with the existing XML document approach can be used when the user taps on the Form Reset activity in the application. That implies, all XML elements which exist in the XML document will be deleted and only lookup tags can exist. Lookup fields and default XML tags filled with default value in the XML document if required.
|
1 2 3 4 5 |
UPDATE xml_table SET xml_table.xml_data = xml_template.xml_data FROM xml_table INNER JOIN xml_template ON xml_table.template_id = xml_template.template_id WHERE id = ? |
Here, xml_table is replacing the XML document from the table xml_template dependent on the XML template_id of the existing XML. A table can contain numerous sorts of forms, and even, it can have various templates in the xml_template table.
Deleting a row with XML is the same as regular delete statements to delete a row from the table. In any case, delete operation with XML can take a little more time rather than normal delete operation. Because XML is a LOB data type in SQL Server, which consumes more space than other data types. However, more server assets will be utilized to execute the statement and a little longer to perform the write operation in the file system as well.
In such scenarios, the XML column needs to be blank. Therefore, that column will be replaced with NULL or ” in the row of the table. But never use the replace function to assign ” value to the row of the table. That activity required on the platform when confidential or private information is deleting from the system and doesn’t want to keep in the product. However, as the audit standards of the system that rows need to exist in the database. Therefore, that XML document will be replaced with the blank or NULL in the SQL Server.
For example:
|
1 2 3 |
UPDATE xml_table SET xml_table.xml_data = '' WHERE id = ? |
In this article, the above XQuery examples explain to delete and replace the value of different XML nodes inside XML, where the replacement XML node value operation can happen more than once in the SQL XML document. This article will give a better understanding of the learning extension over the SQL XML.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020