casting the value from an xml document inside sql server as datetime

Question

casting the value from an xml document inside sql server as datetime

sean-165363

Valued Member

Points: 55
More actions
April 28, 2007 at 4:44 am

#176160

hi there,
I'm trying to format the date that I selecting from an xml document in sql server 2005. The date format I'm trying to insert into another table is (2007-01-04). the date format i'm getting from the document is (2002-09-27). I'm not sure how to cast the xml node as datetime.
Could someone please help me out with the syntax please?
Sean - thanks in advance
SELECT
nref.value('status[1]', 'nvarchar(50)') STATUS,
nref.value('stockCode[1]', 'nvarchar(50)') STOCKCODE,
nref.value('barcode[1]', 'nvarchar(50)') BARCODE,
substring(nref.value('stockName[1]', 'nvarchar(50)'),5,150)+ ' ' + nref.value('stockName2[1]', 'nvarchar(50)') STOCKNAME,
nref.value('unitWeight[1]', 'nvarchar(50)') UNITWEIGHT,
nref.value('tradePrice[1]', 'nvarchar(50)') TRADEPRICE,
nref.value('srpPrice[1]', 'nvarchar(50)') SRPPRICE,
nref.value('format[1]', 'nvarchar(50)') FORMAT,
nref.value('publisher[1]', 'nvarchar(50)') PUBLISHER,
---->CAST(nref.value('eta[1]'), ' as datetime') ETA
/*nref.value('backIn[1]', 'nvarchar(50)') BACKIN,
nref.value('oflc[1]', 'nvarchar(50)') OFLC,
nref.value('retailRating[1]', 'nvarchar(50)') RETAILRATING,
nref.value('maxPlayers[1]', 'nvarchar(50)') MAXPLAYERS,
nref.value('bulky[1]', 'nvarchar(50)') BULKY,
nref.value('boxSet[1]', 'nvarchar(50)') BOXSET,
nref.value('synopsis[1]', 'nvarchar(50)') SYNOPSIS,
nref.value('actors[1]', 'nvarchar(50)') ACTORS,
nref.value('producers[1]', 'nvarchar(50)') PRODUCERS,
nref.value('directors[1]', 'nvarchar(50)') DIRECTORS,
nref.value('audioTracks[1]', 'nvarchar(50)') AUDIOTRACKS,
nref.value('regions[1]', 'nvarchar(50)') REGIONS,
nref.value('subtitles[1]', 'nvarchar(50)') SUBTITLES */
FROM tblFeedStorage CROSS APPLY XMLData.nodes('//product') AS R(nref)
WHERE nref.exist('.[format = "DVM"]')=1
or nref.exist('.[format = "DVD"]') =1
or nref.exist('.[format = "BLR"]') =1
or nref.exist('.[format = "HDD"]') =1
---OR nref.exist('.[format != "PS3"]') = 1

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

DanKennedy Hall of Fame Points: 3736 More actions · Answer 1

sean,

I'm not sure I understand your question, you already seem to have the syntax for returning a datetime from the document but here's my (simpler) example I used to test

DECLARE @xml xml

SET @xml = N'<root><order id="1" description="Lots of stuff" dateordered="2007-6-12"/></root>';

SELECT@xml.value('data(root/order/@description)[1]', 'nvarchar(50)') AS orderdescription,

@xml.value('data(root/order/@dateordered)[1]', 'datetime') AS orderdescription

Dan
www.firstcs.co.uk

sean-165363 Valued Member Points: 55 More actions · Answer 2

hi there,

I tried to the syntax that you suggested and I got the following error. The column is actually smalldatetime, I forgot to mention this orginally.

nref.value('eta[1]', 'smalldatetime') ETA

(1 row(s) affected)

Msg 8115, Level 16, State 2, Line 9

Arithmetic overflow error converting expression to data type smalldatetime.

The statement has been terminated.

DanKennedy Hall of Fame Points: 3736 More actions · Answer 3

Can you post a small sample of the xml data your trying to convert ?

Dan
www.firstcs.co.uk

sean-165363 Valued Member Points: 55 More actions · Answer 4

Hi There,

here is one product from the table, I hope you can help me with this.

thanks again.

<status>UPDATE</status>

<stockCode>DVAHISX</stockCode>

<stockName>DVD American History X</stockName>

<genre>ACTION</genre>

<bulky>false</bulky>

<boxSet>false</boxSet>

<synopsis>RGVyZWsgVmlueWFyZCB3YXNuJ3QgYm9ybiBhIHJhY2lzdCwgYnV0IGhlIGxlYXJudCBxdWlja2x5IGhvdyB0byBiZWNvbWUgb25lLiBTZWVraW5nIHJldHJpYnV0aW9uIGZvciBoaXMgZmF0aGVyJ3MgbXVyZGVyIGFuZCBidXJuaW5nIGZvciBhIHdheSB0byB2ZW50IGhpcyByYWdlLCBEZXJlayAoT3NjYXIgbm9taW5lZSwgRWR3YXJkIE5vcnRvbikgdHVybmVkIGludG8gdGhlIGNoYXJpc21hdGljIHNraW5oZWFkIGxlYWRlciBvZiBhIGxvY2FsIHdoaXRlIHBvd2VyIG1vdmVtZW50LiBGcm9tIHRoZWZ0IGFuZCBhc3NhdWx0LCBEZXJlaydzIHZpb2xlbnQgYWN0aW9ucyBlc2NhbGF0ZWQgaW50byBhbGwtb3V0IHdhciwgY3VsbWluYXRpbmcgaW4gdGhlIGJydXRhbCBtdXJkZXIgb2YgdHdvIHRlZW5hZ2Vycy4gSW4gaGlzIGV5ZXMsIHJhY2UtaGF0ZSBqdXN0aWNlIGhhcyBiZWVuIGRlYWx0LiBOb3cgYWZ0ZXIgdGhyZWUgeWVhcnMgaW4gcHJpc29uLCBldmVyeW9uZSBhd2FpdHMgRGVyZWsncyByZXR1cm47IGhpcyBnYW5nOyBoaXMgdmlvbGVudCBnaXJsZnJpZW5kIC0gYW5kIGhpcyBncmVhdGVzdCBmYW4sIGhpcyBicm90aGVyLCBEYW5ueSAoRWR3YXJkIEZ1cmxvbmcpLiBCdXQgd2hhdCB0aGV5IGFyZSBhYm91dCB0byBlbmNvdW50ZXIgaXMgYSBtYW4gd2hvIG5vIGxvbmdlciB2aWV3cyBoYXRyZWQgYXMgYSBiYWRnZSBvZiBob25vdXIuIEFzaGFtZWQgb2YgaGlzIHBhc3QsIERlcmVrIGlzIGluIGEgcmFjZSB0byBzYXZlIERhbm55IGFuZCBoaXMgZmFtaWx5IGZyb20gdGhlIHZpb2xlbmNlIGhlJ3MgYnJvdWdodCBkb3duIHVwb24gdGhlbS4=</synopsis>

<actor>Fairuza Balk</actor>

<actor>Edward Norton</actor>

<actor>Elliott Gould</actor>

<actor>Avery Brooks</actor>

<actor>Beverly D'Angelo</actor>

<actor>Edward Furlong</actor>

<actor>Stacy Keach</actor>

</actors>

</directors>

</regions>

<subtitle>English</subtitle>

</subtitles>

</product>

DanKennedy Hall of Fame Points: 3736 More actions · Answer 5

sean,

just tried the xml you sent by assigning it to a local variable and executing the following which worked fine

SELECT nref.value('status[1]', 'nvarchar(50)') STATUS,

nref.value('stockCode[1]', 'nvarchar(50)') STOCKCODE,

nref.value('barcode[1]', 'nvarchar(50)') BARCODE,

substring(nref.value('stockName[1]', 'nvarchar(50)'),5,150)+ ' ' + @xml.value('stockName2[1]', 'nvarchar(50)') STOCKNAME,

nref.value('unitWeight[1]', 'nvarchar(50)') UNITWEIGHT,

nref.value('tradePrice[1]', 'nvarchar(50)') TRADEPRICE,

nref.value('srpPrice[1]', 'nvarchar(50)') SRPPRICE,

nref.value('format[1]', 'nvarchar(50)') FORMAT,

nref.value('publisher[1]', 'nvarchar(50)') PUBLISHER,

nref.value('eta[1]', 'smalldatetime') AS ETA

FROM @xml.nodes('//product') AS R(nref)

Perhaps it's something to do with the CROSS APPLY join you're using against the other table ? Sorry I can't be more help but like I say, the above worked fine for me.

Dan
www.firstcs.co.uk

sean-165363 Valued Member Points: 55 More actions · Answer 6

hi there,

it's this nref.value('eta[1]', 'smalldatetime') AS ETA that giving me trouble. The query works up to the point I need to insert some dates then it all falls over. The column that i'm inserting into is smalldatetime.it seems to be only the dates.