September 1, 2008 at 12:07 pm
When I use the xs:dateTime function to convert XML datetime to SQL datetime format, I get NULLs. The below XML stored in a table ( in a XML column),
[data]
[main]
[row number="1" modified="aoMODIFIED"]
[order_id modified="False" type="String"]CS0808240001[/order_id]
[eta_date modified="False" type="DateTime"]2008-08-25T00:00:00[/eta_date]
[eta_time modified="False" type="DateTime"]1900-01-01T10:00:00[/eta_time]
[/row]
[/main]
[/data]
below is the query,
select
nref.value('order_id[1]','varchar(50)') order_id,
nref.value('eta_date[1]','varchar(50)') eta_date_orig,
nref.value('xs:dateTime(eta_date[1])','datetime') eta_date1,
nref.value('xs:dateTime((eta_date)[1])', 'datetime') eta_date
from
escalation_events
cross apply
statexml.nodes('/data/main/row') as p(nref)
I get the following resultset,
order_id | eta_date_orig | eta_date1 | eta_date |
CS0808240001 | 2008-08-25T00:00:00 | NULL | NULL |
any ideas why I am getting nulls from the xs:dateTime() function?
September 1, 2008 at 2:44 pm
I don't think that you need the xs:dateTime function. Try it like this:
select
nref.value('order_id[1]','varchar(50)') order_id,
nref.value('eta_date[1]','varchar(50)') eta_date_orig,
nref.value('eta_date[1])','datetime') eta_date1,
nref.value('(eta_date)[1])', 'datetime') eta_date
from
escalation_events
cross apply
statexml.nodes('/data/main/row') as p(nref)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 2, 2008 at 4:08 am
I kept away from direct conversion because I was getting arithmetic overflow exceptions. I narrowed down to the following row, any ideas, how I can get past this. thanks in advance.
declare @statexml as xml
set @statexml = '<data>
<main>
<row number="1" modified="aoMODIFIED">
<order_id modified="False" type="String">CS0808240001</order_id>
<eta_date modified="False" type="DateTime">2008-08-25T00:00:00</eta_date>
<eta_time modified="False" type="DateTime">0001-01-01T10:00:00</eta_time>
</row>
</main>
</data>'
select
nref.value('order_id[1]','varchar(50)') order_id,
nref.value('eta_date[1]','datetime') eta_date_orig,
nref.value('eta_time[1]','datetime') eta_time_orig
from
@statexml.nodes('/data/main/row') as p(nref)
this should throw the following exception
Arithmetic overflow error converting expression to data type datetime.
September 2, 2008 at 9:37 am
Yeah, that is an illegal datetime value.
Look at the output from this slightly modified example:
declare @statexml as xml
set @statexml = '<data>
<main>
<row number="1" modified="aoMODIFIED">
<order_id modified="False" type="String">CS0808240001</order_id>
<eta_date modified="False" type="DateTime">2008-08-25T00:00:00</eta_date>
<eta_time modified="False" type="DateTime">1900-01-01T10:00:00</eta_time>
</row>
</main>
</data>'
select
nref.value('order_id[1]','varchar(50)') order_id,
nref.value('eta_date[1]','datetime') eta_date_orig
, nref.value('eta_time[1]','datetime') eta_time_orig
, Cast('10:00:00' as datetime)
-- , Cast('0001-01-01 10:00:00' as datetime)
from
@statexml.nodes('/data/main/row') as p(nref)
Now, uncomment the fifth column of the SELECT and try it again.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply