Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lokii
Starting Member
1 Post |
 Posted - 2009-07-29 : 19:46:47
|
| I need to get the date out from the xml file inside a xml datatype column and then compare the date with current dateExample -content_html(column name)<root><vidDate>2004-09-27</vidDate></root> row 1<root><vidDate>2004-09-28</vidDate></root> row 2<root><vidDate>2004-09-29</vidDate></root> row 3I wrote the script as below -select cast(content_html as xml).query('(/root/vidDate/text())') from content where cast(content_html as xml).value('((/root/vidDate/text()))','datetime') > getdate()order by cast(content_html as xml).value('(/root[1]/vidDate[1])','nvarchar(255)') descTHis is throwing an errorXQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'Any help is greatly appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-30 : 14:35:24
|
| [code]select t.u.value('./vidDate[1]','datetime')from yourtable cross apply content_html.nodes('/root') t (u)[/code] |
 |
|
|
|
|
|
XML datatype to date casting