Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Update XML puzzle using SQL

In this puzzle you have to update the value present in XMLs for Id = 1 and Id = 3 from existing values to Avtaar and Pawan respectively.

Please check the sample input and the expected output.

Sample Input


Id          XmlVal
----------- ------------------------------------------------------------
1           <Data><a id="1">1</a><b>21</b><c>31</c></Data>
2           <Data><a id="1">1</a><b>22</b><c>32</c></Data>
3           <Data><a id="2">1</a><b>23</b></Data>

Expected Output


Id          XmlVal
----------- ------------------------------------------------------------
1           <Data><a id="1">Avtaar</a><b>21</b><c>31</c></Data>
2           <Data><a id="1">1</a><b>22</b><c>32</c></Data>
3           <Data><a id="2">Pawan</a><b>23</b></Data>

(3 rows affected)

--

Use below script to create table and insert sample data into it.

--

CREATE TABLE XMLUpdate 
( 
	  Id INT
	, XmlVal XML 
)
GO

INSERT  INTO XMLUpdate VALUES  
( 1, N'<Data><a id="1">1</a><b>21</b><c>31</c></Data>' ),
( 2, N'<Data><a id="1">1</a><b>22</b><c>32</c></Data>' ),
( 3, N'<Data><a id="2">1</a><b>23</b></Data>' )
GO

SELECT * FROM XMLUpdate
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1

--

UPDATE  XMLUpdate
SET     XmlVal.modify('replace value of (/Data/a[@id="1"]/text())[1] with "Avtaar"')
WHERE Id = 1

UPDATE  XMLUpdate
SET     XmlVal.modify('replace value of (/Data/a[@id="2"]/text())[1] with "Pawan"')
WHERE   ID = 3

SELECT  * FROM XMLUpdate
GO

--

Output-1

--                            

Id          XmlVal
----------- ------------------------------------------------------------
1           <Data><a id="1">Avtaar</a><b>21</b><c>31</c></Data>
2           <Data><a id="1">1</a><b>22</b><c>32</c></Data>
3           <Data><a id="2">Pawan</a><b>23</b></Data>

(3 rows affected)

--

References

https://docs.microsoft.com/en-us/sql/t-sql/xml/replace-value-of-xml-dml
https://docs.microsoft.com/en-us/sql/t-sql/xml/modify-method-xml-data-type

Full SET of related XML Puzzles

1 https://msbiskills.com/2018/02/24/sql-puzzle-check-node-existence-in-xml-using-sql/
2 https://msbiskills.com/2018/02/24/sql-puzzle-read-xml-data-flat-no-inner-elements-using-sql/
3 https://msbiskills.com/2018/02/22/sql-puzzle-read-and-pivot-xml-data-while-reading-in-sql/
4 https://msbiskills.com/2018/02/22/sql-puzzle-multiple-ways-to-read-xml-datawith-attributes-single-multi-using-sql-2/
5 https://msbiskills.com/2018/02/21/sql-puzzle-read-data-from-xml-1/
6 https://msbiskills.com/2018/02/20/sql-puzzle-handling-special-characters-with-for-xml-path-puzzle/
7 https://msbiskills.com/2018/02/18/sql-puzzle-get-latest-record-from-xml/
8 https://msbiskills.com/2018/01/19/sql-puzzle-split-string-via-on-dot-using-xml/
9 https://msbiskills.com/2017/12/15/sql-puzzle-generate-xml-puzzle/
10 https://msbiskills.com/2017/10/27/sql-puzzle-the-xml-node-copy-puzzle/
11 https://msbiskills.com/2017/10/18/sql-puzzle-the-xml-explicit-puzzle/
12 https://msbiskills.com/2016/11/28/sql-puzzle-the-xml-path-puzzle/
13 https://pawankkmr.wordpress.com/2015/03/25/t-sql-query-group-by-xml-path-puzzle/
14 https://pawankkmr.wordpress.com/2015/03/25/t-sql-query-covert-comma-separated-values-to-a-table-using-cross-apply-xml-puzzle/

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com