CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlserialization
Michał Kuliński
Michał Kuliński

Reputation: 1976

How to deserialize data records serialized with FOR XML AUTO in T-SQL?

In my Microsoft SQL Server 2005 database I have data in column WorkingDays like :

<WorkingDays>
  <Day>2011-05-05</Day>
  <Day>2011-05-06</Day>
</WorkingDays>

How to deserialize them within T-SQL query to DateTime format?

enter image description here

Upvotes: 5

Views: 3523

Answers (1)

marc_s
marc_s

Reputation: 754468

Try something like this:

DECLARE @TEST TABLE(ID INT IDENTITY, XmlContent XML)

INSERT INTO @TEST(XmlContent) VALUES('<WorkingDays>
  <Day>2011-05-05</Day>
  <Day>2011-05-06</Day>
</WorkingDays>')


SELECT 
    ID,
    WD.DAYS.value('(.)[1]', 'datetime') AS 'Day'
FROM 
    @TEST
CROSS APPLY 
    XmlContent.nodes('/WorkingDays/Day') AS WD(Days)

This gives me an output of:

ID    Day
1     2011-05-05 00:00:00.000
1     2011-05-06 00:00:00.000

Upvotes: 4

Related Questions