I'm using consecutive two Common Table Expression and need to update the XML in a table.
Table schema: StudentMark:
CREATE TABLE [dbo].[StudentMark]
(
[StudentMarkId] [int] IDENTITY(1,1) NOT NULL,
[StudentId] [uniqueidentifier] NOT NULL,
[SubjectId] [uniqueidentifier] NOT NULL,
[Score] [int] NOT NULL,
[ScoreInfo] [xml] NOT NULL,
[GeneratedOn] [datetime2](2) NOT NULL,
[IsPass] [bit] NOT NULL,
CONSTRAINT [PK_StudentMark]
PRIMARY KEY CLUSTERED ([StudentMarkId] ASC)
) ON [PRIMARY]
Sample seed data
INSERT INTO [dbo].[StudentMark] ([StudentId], [SubjectId], [ScoreInfo], [GeneratedOn], [Score], [IsPass])
VALUES ('FC3CB475-B480-4129-9190-6DE880E2D581', '0D72F79E-FB48-4D3E-9906-B78A9D105081', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-10 10:10:15', 95, 1),
('0F4EF48C-93E3-41AA-8295-F6B0E8D8C3A2', '0D72F79E-FB48-4D3E-9906-B78A9D105081', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-10 10:10:15', 100, 1),
('0F4EF48C-93E3-41AA-8295-F6B0E8D8C3A2', 'AB172272-D2E9-49E1-8040-6117BB6743DB', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-16 09:06:20', 25, 0),
('FC3CB475-B480-4129-9190-6DE880E2D581', 'AB172272-D2E9-49E1-8040-6117BB6743DB', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-16 09:06:20', 82, 1);
Requirement: I need to create a XML and update the generated XML into the respective Row's column [dbo].[StudentMark][ScoreInfo]
XML Template:
<Score>
<Meta>
<StudentMarkId>1</StudentMarkId>
<StudentId>FC3CB475-B480-4129-9190-6DE880E2D581</StudentId>
<SubjectId>0D72F79E-FB48-4D3E-9906-B78A9D105081</SubjectId>
</Meta>
<ScoreInfo>
<Score>95</Score>
<GeneratedOn>2017-08-10 10:10:15</GeneratedOn>
<IsPass>1</IsPass>
</ScoreInfo>
</Score>
I tried the following script
DECLARE @cnt INT = 0
WHILE @cnt < 4
BEGIN
WITH Student ([StudentMarkId], [StudentId], [SubjectId], [Score], [GeneratedOn], [IsPass]) AS
(
SELECT [StudentMarkId], [StudentId], [SubjectId], [Score], [GeneratedOn], [IsPass] FROM [dbo].[StudentMark]
ORDER BY [StudentMarkId]
OFFSET @cnt ROWS
FETCH NEXT 1 ROWS ONLY
),
ScoreInfo ([ScoreXML]) AS (
SELECT (SELECT [StudentMarkId], [StudentId], [SubjectId] FROM Student FOR XML RAW, ELEMENTS) AS Meta,
(SELECT [Score], [GeneratedOn], [IsPass] FROM Student FOR XML RAW, ELEMENTS) AS ScoreInfo
FOR XML RAW ('ScoreInfo'), ELEMENTS
)
UPDATE [dbo].[StudentMark]
SET [ScoreInfo] = ScoreInfo ???
WHERE ???
SET @cnt = @cnt + 1;
END
Kindly assist me how to update this.