In the article you had written that we need to give time zone information while creating xml for a date or time field. In this case when we use xpath query to get the value of a date field via following query I am getting a error message.

   x.item.value('Employee\@ReportingTime[1]', 'Datetime') AS ReportingTime

   Let say ReportingTime as attribute for a Employee element.

That message is because I am casting a value like '1975-03-14+05:30' in Datetime in Sql Server and sql server donot store timezone information in the field itself.

I have only option to use string datatype in schema

Is there any other method ....

dinesh,

I did not notice your message until this morning (when i get into this page accidently), because this article is not yet published. It is scheduled and will be out soon.

You need to apply an 'XQuery' conversion before the value can be converted to a valid SQL Server datetime value. Use the function "xs:dateTime()" or "xs:date()" for the conversion. Here is an example.

DECLARE @emp AS XML

SET @emp = '

<Employee EmployeeNumber="1001" Language="EN" >

<FullName>Jacob</FullName>

<Salary>10000</Salary>

<Age>30</Age>

<Married>1</Married>

<BirthDate>1975-03-14T12:00:00+05:30</BirthDate>

<ReportingTime />

</Employee>

'

SELECT

x.e.value('@EmployeeNumber[1]','varchar(20)') as EmpNumber,

x.e.value('FullName[1]','varchar(40)') as FullName,

x.e.value('xs:dateTime(BirthDate[1])', 'datetime') as BirthDate

@emp.nodes('Employee') x(e)

Hello Jacob,

You gave really useful information in easy way with example. Can you post the way to deal with Enumeration (for example weekdays Sunday, Monday,... Saturday) in XML Schema validation?

Thank you once again for your articles.

Hi Herit,

There is an article in this series which explains the enumeration in detail.

I am not sure what is the scheduled date. But I guess it will be out in a week.

thanks

Jacob

 DECLARE @emp AS XML(EmployeeSchema)
   SET @emp = ''

 DECLARE @emp AS XML(EmployeeSchema)
   SET @emp = 'put a valid xml comment here'

DECLARE @x XML(DOCUMENT EmployeeSchema)

Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'year'.