Convert values stored in XML into ntext and date types in SQL Server 2005

Question

I'm working on a stored procedure for a calendar application. Each event in the calendar can have several dates. This information is stored in two different tables. Rather than write two stored procedures and call the second one multiple times to save the dates, I'd rather just pass them in using XML. The trouble is that I want to convert some of the values into date types and one of them into an ntext type.

Here's a simple example what I have so far:

declare @samplexml as xml
set @samplexml = '<root><scheduleRow><dateBegin>4/5/2009</dateBegin><dateEnd>4/6/2009</dateEnd><timeBegin>1:00 pm</timeBegin><timeEnd>2:00 pm</timeEnd><location>Sheas House</location></scheduleRow><scheduleRow><dateBegin>5/5/2009</dateBegin><dateEnd>5/6/2009</dateEnd><timeBegin>2:00 pm</timeBegin><timeEnd>3:00 pm</timeEnd><location>Metro Buffet</location></scheduleRow></root>'

select x.scheduleRow.value('./dateBegin[1]','varchar(20)') as date_begin,
       x.scheduleRow.value('./dateEnd[1]','varchar(20)')   as date_end,
       x.scheduleRow.value('./timeBegin[1]','varchar(20)') as time_begin,
       x.scheduleRow.value('./timeEnd[1]','varchar(20)')   as time_end,
       x.scheduleRow.value('./location[1]','ntext')        as location
  from @samplexml.nodes('/root/scheduleRow') as x(scheduleRow)

If you try to run this, you'll get this error:

Msg 9500, Level 16, State 1, Line 4 The data type 'ntext' used in the VALUE method is invalid.

What am I doing wrong here? Is there any way to do what I want?

Answer 1

Don't use TEXT, NTEXT and IMAGE type for any new development. They are deprecated and will be removed on a future release of SQL. Even since SQL 2k5 they were no longer supported on any new feature, case in point XML methods.

Use VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) instead.