We have seen a few examples that demonstrated how to delete elements and attributes from XML documents. In this lab, let us see how to delete rows from a table, based on the data in an XML document.
DECLARE @t TABLE (id INT)INSERT INTO @t(id) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3SELECT * FROM @t /*id-----------123*/declare @XmlData xml set @XmlData = '<PersonalInformationObject> <Skills> <SkillObject> <SkillId>1</SkillId> </SkillObject> <SkillObject> <SkillId>2</SkillId> </SkillObject> </Skills></PersonalInformationObject>'DELETE tFROM @t tCROSS APPLY @XmlData.nodes(' /PersonalInformationObject/Skills/SkillObject/SkillId [. = sql:column("id")]') a(x)SELECT * FROM @t /*id-----------3*/
Note the usage of CROSS APPLY and the way the join is established between the table and the XML document using the sql:column() function within the XQuery expression.
Previous Lab: XQuery Lab 48 - Sorting Query files in SQL Server Management Studio (SSMS) Solution/Project
View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials
Tags: XQuery-Functions, XQuery-Labs, XML, XQuery, XQuery Functions, SQL Server XQuery, XQuery in TSQL, XQuery Training, XQuery Tutorial,