December 30, 2008 at 7:09 pm
I just ran into another issue with creating xml with xquery. This time I can't reference a datetime field through the use of sql:column.
Here's my code snippet:
element Visit
{
element Date { sql:column("ThisDate") }
},
This returns the following error:
Msg 6739, Level 16, State 1, Line 28
XQuery: SQL type 'datetime' is not supported in XQuery.
I even tried casting it, but I get the same error!
element Visit
{
element Date { sql:column("ThisDate") cast as xs:string? }
},
Anyone have any ideas?
December 31, 2008 at 8:17 am
Have you tried using a CTE? CONVERT the datetime column to a string in the cte, and then build your xml off the CTE instead of the base table.
-------------------------------------------------------------------------
declare @x xml
declare @forms Table (PhyFName varchar(20), PhyMName varchar(20), PhyLName varchar(20), PhySuffix varchar(5), ThisDate datetime)
set @x = ''
insert into @forms
select 'John','Jacob','Jones','Esq.',GETDATE()
select * from @forms
;with cteForms as
(select PhyFName,PhyMName,PhyLName,PhySuffix,convert(varchar(30),ThisDate,101) as ThisDate
from @forms
)
select @x.query('
element Practice
{
element Physician
{
element Name
{
element First { sql:column("PhyFName") },
element Middle { sql:column("PhyMName") },
element Last { sql:column("PhyLName") },
element Suffix { sql:column("PhySuffix") },
element Date{ sql:column("ThisDate") }
}
}
}
')
from cteForms
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 31, 2008 at 9:50 am
Have you tried simply using SQLXML? Using XQuery for this in SQL is reminiscent of buying a hammer and some nails, but then throwing away the hammer and using your head to put the nails in instead...:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 31, 2008 at 10:59 am
Related thread over here:
http://www.sqlservercentral.com/Forums/Topic627764-338-1.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 31, 2008 at 3:01 pm
Matt Miller (12/31/2008)
Have you tried simply using SQLXML? Using XQuery for this in SQL is reminiscent of buying a hammer and some nails, but then throwing away the hammer and using your head to put the nails in instead...:)
Heh. That would explain all the dents in my forehead! :crazy:
[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]
December 31, 2008 at 6:16 pm
Yes, XML does not fully support datetime conversions in SQL 2005 (fixed in 2008, I think). I always just do the date to/from text conversions in SQL, outside of the XML.
[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]
January 1, 2009 at 1:38 pm
The more I see about the problems of XML in a database, whether real or percieved, the more happy I am that I've taken the very bitter and hardline stance of not storing any XML in the database for any reason.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2009 at 1:43 pm
The best thing I can say for storing XML in the database is that it let us store the original XML received when a policy decision was made to not store all elements as discrete columns in the database. Later on, when certain data items were determined to be significant, we were able to create additional columns and populate them from the original XML.
This also keeps us from losing data if the application that generates the XML ever changes. (I know, I know.... it should never change without prior notification and approval.... and yet. )
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 25, 2009 at 8:16 am
I ran in the exact same issue and have filed it as a bug on microsoft's connect site. I found that SQL server 2008 does support datetime in sql:variable(), but sql server 2005 does not. For it to be fixed in sql server 2005 the bug report needs your votes: please vote at: microsoft connect.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply