Insert element into XML using XQuery in SQL Server

Question

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>test@sample.com</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.

Looks like your variable naming is all over the place in your example, you have variables named "@ContactDetails" in your SP which don't exist, "//EmailDetails/IslConfirmed)[1]" has an incorrect node name, " INSERT INTO dbo.emails" has the wrong table name. Other than that when you tidy it up it works fine, are you sure you aren't missing showing us something else? — Jamie Pollard, Aug 19 '15 at 12:39
Hi Jamie, Sorry about the typo. I have edited the page. I want to know the syntax for my Input XMl mentioning as (Document ). I dont know much about XML. Could you please help me to explain it? — salaiviswa, Aug 19 '15 at 13:04

score 1 · Accepted Answer · answered Aug 19 '15 at 13:17

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

Hi Jamie, Thanks a lot for your valuable suggestion. You catched up my requirements and provided solution accurately. This works like a charm! Million thanks to you! :) — salaiviswa, Aug 21 '15 at 05:16

Insert element into XML using XQuery in SQL Server

1 Answers1