FOR XML from Common Table Expression - SQL Server

63 views Asked by At

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.

0

There are 0 answers