Converting XML Datetime to SQL Datetime

Question

I have an XML datatype and want to convert an element into a sql DateTime variable. How?

e.g.

Declare @Xml Xml
Set @Xml = '<Root><DateFrom>2008-10-31T00:00:00</DateFrom></Root>'

Declare @DateFrom DateTime
Set @DateFrom = ?????

How can I set @DateFrom with the date from the xml above?

Answer 1

Declare @Xml Xml
Set @Xml = '<Root><DateFrom>8/10/2008</DateFrom></Root>'

Declare @DateFrom DateTime
Select @DateFrom= t.b.value('DateFrom[1]', 'datetime')
from @xml.nodes('//Root') t(b)

I highly recommend you look at the .nodes functionality of the XML data type. The above code pulls out the value of the DateFrom element and converts it to Date Time format.