Insert element into XML using XQuery in SQL Server

I need to insert a column in a stored procedure which reads XML as input and reads the XML value using XQuery in SQL Server.

Consider a table dbo.emailDetails

Create Table dbo.emailDetails
(
     EmailId int,
     HomeEmail varchar(250),
);

XML file:

<EmailDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <EmailId>1</EmailId>
    <HomeEmail>[email protected]</HomeEmail>
</EmailDetails>

SQL Server stored procedure:

CREATE PROCEDURE [apply].[UpdateEmailDetails]
     @EmailDetails XML
 (DOCUMENT [dbo].[EmailDetails])
AS 
BEGIN
    DECLARE @EmailId INT, @HomeEmail NVARCHAR(250)

    SET @HomeEmail = @EmailDetails.value(N'(//EmailDetails/HomeEmail)[1]',N'NVARCHAR(255)');
    SET @EmailId = @EmailDetails.value(N'(//EmailDetails/EmailID)[1]',N'INT');

    INSERT INTO dbo.emails (emailid, homeemail) 
    VALUES (@EmailId, @HomeEmail )
END

Now I would like to include the IsConfirmed in the above stored procedure.

 alter table dbo.Emaildetails
    add IsConfirmed bit not null default 0

----- Executed successfully and added the new column in the table.

While I tried the below changes in the stored procedure:

Declare @IsConfirmed bit
SET @IsConfirmed = @EmailDetails.value(N'(//EmailDetails/IsConfirmed)[1]',N'BIT');

and got the error:

XQuery [value()]: There is no element named 'IsConfirmed' in the type 'element(EmailDetails,#anonymous) *'.

Could someone help me with this?

Kindly let me know if any details required further.

Regards, Viswa V.

Solution

Your stored procedure has an input parameter which is as follows:

@EmailDetails XML (DOCUMENT [dbo].[EmailDetails])

This means you have an XML Schema Collection named "[dbo].[EmailDetails]" defined which basically says what format the incoming XML should be in.

If you expand the Programmability > Types > XML Schema Collections nodes in your database explorer you will see your "[dbo].[EmailDetails]" schema. If you then right click and select Script as > Create to new window you will see what nodes are expected to be in the XML you pass to your procedure.

You need to ALTER this schema to include a definition for your new

<IsConfirmed>

node.

Once you alter it you should then be able to run your alter procedure command again.

This MSDN article explains more on the topic