SELECT
	YourOtherColumns,	
	c.value('date_from[1]','datetime')
FROM
	requestpropertydata y
	CROSS APPLY 
		y.propertyvalue.nodes('/Absence') T(c)

declare @x table
(
x xml
)

insert @x
values
('<Absence>
<date_from>2013-08-19T00:00:00+01:00</date_from>
<date_to>2013-08-19T00:00:00+01:00</date_to>
<absence_type>AAWS</absence_type>
<represents>S</represents></Absence>')


SELECT t.u.value('(./date_from)[1]','datetime') as date_from,
t.u.value('(./date_to)[1]','datetime') as date_to,
t.u.value('(./absence_type)[1]','varchar(15)') as absence_type,
t.u.value('(./represents)[1]','varchar(5)') as represents
FROM @x t1
CROSS APPLY x.nodes('Absence')t(u)


output
----------------------------------
date_from	date_to	absence_type	represents
2013-08-18 23:00:00.000	2013-08-18 23:00:00.000	AAWS	S


NB:- I've updated your XML value to make it well formed (it was missing closing tag for Absence)

quote:

---

Originally posted by KatiSQL

Thank you for quick reply

I have run it but I have got error message
The XMLDT method 'nodes' can only be invoked on columns of type xml.

I have checked the column and the type is varchar.

I can not change the data structure so any idea how to get the value??

Cheers

---

declare @x table
(
x varchar(8000)
)

insert @x
values
('<Absence>
<date_from>2013-08-19T00:00:00+01:00</date_from>
<date_to>2013-08-19T00:00:00+01:00</date_to>
<absence_type>AAWS</absence_type>
<represents>S</represents></Absence>')


SELECT t.u.value('(./date_from)[1]','datetime') as date_from,
t.u.value('(./date_to)[1]','datetime') as date_to,
t.u.value('(./absence_type)[1]','varchar(15)') as absence_type,
t.u.value('(./represents)[1]','varchar(5)') as represents
FROM (SELECT CAST(x AS xml) AS x FROM @x) t1
CROSS APPLY x.nodes('Absence')t(u)





output
----------------------------------
date_from	date_to	absence_type	represents
2013-08-18 23:00:00.000	2013-08-18 23:00:00.000	AAWS	S


NB:- I've updated your XML value to make it well formed (it was missing closing tag for Absence)