Anything to do with handling XML data can be tricky in SQL Server. Here are some examples of how to insert/update/delete elements. I have chosen to use the sql:variable to demonstrate how dynamic elements can be used in these functions.
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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
-- DECLARE A BASIC XML DOCUMENT DECLARE @xml XML SET @xml = '<root> <people> <person name="richard"> <age>20</age> </person> <person name="michael"> <age>25</age> </person> <person name="davidson"> <age>30</age> </person> </people> </root>'; DECLARE @name VARCHAR(20) = 'michael' ,@age VARCHAR(20) = '100000' SELECT @xml.value('((/root/people/person)[@name = sql:variable("@name")]/age)[1]','varchar(20)') as SelectVariable -- 1. Update the age SET @xml.modify(' replace value of ((/root/people/person)[@name = sql:variable("@name")]/age/text())[1] with sql:variable("@age") ') SELECT @xml AS AgeUpdated -- 2. Delete SET @xml.modify(' delete (/root/people/person[age/text() = sql:variable("@age")])[1] ') -- New XML to insert DECLARE @insert XML = '<person><age>41</age></person>' SET @xml.modify(' insert sql:variable("@insert") after (/root/people/person)[1] ') SET @insert = '<person><age>40</age></person>' SET @xml.modify(' insert sql:variable("@insert") after (/root/people/person)[1] ') SET @insert = '<person><age>39</age></person>' SET @xml.modify(' insert sql:variable("@insert") after (/root/people/person)[1] ') SELECT @xml AS AfterAdditions |
