Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Workshop VII - Validating values with SCHEMA


XML Workshop VII - Validating values with SCHEMA

Author
Message
jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 2523
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3120.asp

.
dinesh sodani
dinesh sodani
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 38

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 ....

 


jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 2523

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

FROM

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



.
Harit Gohel
Harit Gohel
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 Visits: 232

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.


Mikael Eriksson SE
Mikael Eriksson SE
SSChasing Mays
SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)

Group: General Forum Members
Points: 624 Visits: 944
Do you have any idea why there is a requirement for timezone on date and time fields?

According to W3C timezone is optional on date, time and datetime fields.
http://www.w3.org/TR/xmlschema-2/#dateTime

/Micke



jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 2523

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



.
tengtium
tengtium
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 91
using the article example.


this is empty xml (assuming it's the content of the file is coming from outside, meaning the content was read in an .xml file
 DECLARE @emp AS XML(EmployeeSchema)
SET @emp = ''



this is empty xml (assuming it's the content of the file is coming from outside, meaning the content was read in an .xml file

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



why to two last statement is valid??? why an empty xml or an xml with valid xml comment cannot be validated by predefined schema.

is there a way to validated an empty xml or an xml file which is the only content is a valid xml comments?

please help.



jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 2523
XML data type can store XML DOCUMENTS (having exactly 1 root element) and XML CONTENT (having 0 or more top level elements). The default is CONTENT and hence it allows 0 or more top level elements. The schema performs a validation only if the element is present.

Declare your XML variable as DOCUMENT and then SQL Sever will perform the validation that you expect. For example:


DECLARE @x XML(DOCUMENT EmployeeSchema)



.
tengtium
tengtium
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 91
great thanks for the info. i have another problem. i'm trying to use a xsd validation again with regular expression, i got the regex from regexlib and it's great when i'm testing the xml using xsd validation tool. but when i try to create xsd collection in sql i got an error.

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



xsd collection attached



Attachments
xsd.txt (9 views, 6.00 KB)
jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 2523
The regular expression language is documented here: http://www.w3.org/TR/xmlschema-2/#regexs
The documentation is not very easy to understand but you might be able to spot the problem with your syntax.

I have included a detailed RegularExpression tutorial in my XSD book which will be out in a few days. Keep a watch at http://www.sqlservercentral.com/articles/books/65843/

.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search