XML and JSON making simple changes

CREATE TABLE XmlAndJson
(
	id INTEGER IDENTITY NOT NULL,
	XmlData XML,
    JsonData NVARCHAR(MAX)
);
GO
ALTER TABLE XmlAndJson
  ADD CONSTRAINT [JsonData record should be formatted as JSON]
  CHECK (ISJSON(JsonData)=1);

GO
SET NOCOUNT ON;
DECLARE @XmlData as XML;
set @XmlData = '<employees>
  <employee>
    <firstName>Steve</firstName>
	<lastName>Stedman</lastName>
  </employee>
  <employee>
    <firstName>Derrick</firstName>
	<lastName>Bovenkamp</lastName>
  </employee>
  <employee>
    <firstName>Bill</firstName>
	<lastName>Simpkins</lastName>
  </employee>
</employees>';

DECLARE @JsonData as VARCHAR(MAX);
set @JsonData = '
{"employees":[
  { "firstName":"Steve", "lastName":"Stedman" },
  { "firstName":"Derrick", "lastName":"Bovenkamp" },
  { "firstName":"Bill", "lastName":"Simpkins" }
]}';

INSERT INTO XmlAndJson(XmlData, JsonData)
VALUES (@XmlData, @JsonData );

 -- modifying data, first check the row we are going to change.
 SELECT JSON_VALUE(JsonData, '$.employees[0].firstName') as FirstName,
        JSON_VALUE(JsonData, '$.employees[0].lastName') as LastName,
		*
  FROM XmlAndJson
  WHERE ID = 1;

SELECT XmlData.value('(/employees/employee/firstName)[1]', 'varchar(max)') as FirstName,
	   XmlData.value('(/employees/employee/lastName)[1]', 'varchar(max)') as LastName,
	   *
  FROM XmlAndJson
  WHERE ID = 1;

--JSON_MODIFY
DECLARE @json as NVARCHAR(MAX);

SELECT @json = JsonData
  FROM XmlAndJson
 WHERE ID = 1;

-- change firstname
SET @json = JSON_MODIFY(@json, '$.employees[0].firstName', 'Ben') ;

-- change lastname
SET @json = JSON_MODIFY(@json, '$.employees[0].lastName', 'Franklin') ;

-- add age 
SET @json = JSON_MODIFY(@json, '$.employees[0].age', '314') ;

-- add hobby
SET @json = JSON_MODIFY(@json, '$.employees[0].hobby', 'Kites') ;

SELECT @json;

UPDATE XmlAndJson
   SET JsonData = @json
 WHERE ID = 1;

SELECT JSON_VALUE(JsonData, '$.employees[0].firstName') as FirstName,
       JSON_VALUE(JsonData, '$.employees[0].lastName') as LastName,
       JSON_VALUE(JsonData, '$.employees[0].age') as Age,
       JSON_VALUE(JsonData, '$.employees[0].hobby') as Hobby,
	   *
  FROM XmlAndJson
 WHERE ID = 1;

--XML Modify
DECLARE @xml as XML;

SELECT @xml = XmlData
  FROM XmlAndJson
 WHERE ID = 1;

 
-- change firstname
SET @xml.modify('replace value of (/employees/employee/firstName/text())[1] with "Ben"');

-- change lastname
SET @xml.modify('replace value of (/employees/employee/lastName/text())[1] with "Franklin"');

-- add age 
SET @xml.modify('insert <age>314</age> into (/employees/employee)[1]');

-- add hobby
SET @xml.modify('insert <hobby>Kites</hobby> into (/employees/employee)[1]');

SELECT @xml;

UPDATE XmlAndJson
   SET XmlData = @xml
 WHERE ID = 1;

 SELECT XmlData.value('(/employees/employee/firstName)[1]', 'varchar(max)') as FirstName,
		XmlData.value('(/employees/employee/lastName)[1]', 'varchar(max)') as LastName,
		XmlData.value('(/employees/employee/age)[1]', 'varchar(max)') as Age,
		XmlData.value('(/employees/employee/hobby)[1]', 'varchar(max)') as Hobby,
		*
  FROM XmlAndJson
  WHERE ID = 1;