We have numerous XML documents which contain dates in the format dd/mm/yyyy, for example:
<tag>13/01/2013</tag>
However, this date format does not appear to be recognised by the xs:date() constructor function in the following T-SQL, with an error occuring:
DECLARE
@bit bit,
@xml xml =
'
<request xmlns="http://tempuri.com/request.xsd">
<dates>
<mydate>13/01/2013</mydate>
<mydate>14/01/2013</mydate>
<mydate>15/01/2013</mydate>
</dates>
</request>
'
SET @bit = @xml.exist('declare namespace ns="http://tempuri.com/request.xsd"; //ns:mydate[xs:date(text()[1]) cast as xs:date? = xs:date("13/01/2013")]')
SELECT @bit
Error:
Msg 9319, Level 16, State 1, Line 14
XQuery [exist()]: Static simple type validation: Invalid simple type value '13/01/2013'.
Dates in the format yyyy-mm-dd work fine, however modifying the tool that generates the XML is not currently an option. Can anyone please suggest how I can update the XQuery to deal with date values in the format dd/mm/yyyy? This is for a SQL Server stored
procedure.
Member
107 Points
347 Posts
Converting date format in XQuery for xs:date() constructor function
Jun 12, 2013 04:48 AM|zoggling|LINK
We have numerous XML documents which contain dates in the format dd/mm/yyyy, for example:
<tag>13/01/2013</tag>
However, this date format does not appear to be recognised by the xs:date() constructor function in the following T-SQL, with an error occuring:
DECLARE @bit bit, @xml xml = ' <request xmlns="http://tempuri.com/request.xsd"> <dates> <mydate>13/01/2013</mydate> <mydate>14/01/2013</mydate> <mydate>15/01/2013</mydate> </dates> </request> ' SET @bit = @xml.exist('declare namespace ns="http://tempuri.com/request.xsd"; //ns:mydate[xs:date(text()[1]) cast as xs:date? = xs:date("13/01/2013")]') SELECT @bitError:
Msg 9319, Level 16, State 1, Line 14
XQuery [exist()]: Static simple type validation: Invalid simple type value '13/01/2013'.
Dates in the format yyyy-mm-dd work fine, however modifying the tool that generates the XML is not currently an option. Can anyone please suggest how I can update the XQuery to deal with date values in the format dd/mm/yyyy? This is for a SQL Server stored procedure.
Member
107 Points
347 Posts
Re: Converting date format in XQuery for xs:date() constructor function
Jun 13, 2013 03:31 AM|zoggling|LINK
If only fn:tokenize was supported by XQuery in SQL Server... then we might have a solution:
http://nativexmldatabase.com/2011/03/18/xquery-and-sqlxml-how-to-convert-a-date-that-is-not-a-date-into-a-date/
http://msdn.microsoft.com/en-us/library/hh487028%28v=sql.105%29.aspx
http://www.w3.org/TR/xpath-functions/#func-dateTime
Star
9903 Points
1291 Posts
Re: Converting date format in XQuery for xs:date() constructor function
Jun 13, 2013 04:37 AM|Pengzhen Song - MSFT|LINK
Hi,
We are happy that your issue has been resolved.
Member
107 Points
347 Posts
Re: Converting date format in XQuery for xs:date() constructor function
Jun 17, 2013 07:19 AM|zoggling|LINK
It hasn't! I am using SQL Server 2012 and don't have a suitable workaround yet...
Any ideas?