title: Convert values stored in XML into ntext and date types in SQL Server 2005
body: <p>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.</p>
  
  <p>Here's a simple example what I have so far:</p>
  
  <pre><code>declare @samplexml as xml
  set @samplexml = '&lt;root&gt;&lt;scheduleRow&gt;&lt;dateBegin&gt;4/5/2009&lt;/dateBegin&gt;&lt;dateEnd&gt;4/6/2009&lt;/dateEnd&gt;&lt;timeBegin&gt;1:00 pm&lt;/timeBegin&gt;&lt;timeEnd&gt;2:00 pm&lt;/timeEnd&gt;&lt;location&gt;Sheas House&lt;/location&gt;&lt;/scheduleRow&gt;&lt;scheduleRow&gt;&lt;dateBegin&gt;5/5/2009&lt;/dateBegin&gt;&lt;dateEnd&gt;5/6/2009&lt;/dateEnd&gt;&lt;timeBegin&gt;2:00 pm&lt;/timeBegin&gt;&lt;timeEnd&gt;3:00 pm&lt;/timeEnd&gt;&lt;location&gt;Metro Buffet&lt;/location&gt;&lt;/scheduleRow&gt;&lt;/root&gt;'
  
  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) 
  </code></pre>
  
  <p>If you try to run this, you'll get this error:</p>
  
  <p>Msg 9500, Level 16, State 1, Line 4
  <strong>The data type 'ntext' used in the VALUE method is invalid.</strong></p>
  
  <p>What am I doing wrong here? Is there any way to do what I want?</p>