Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

XML Workshop VII - Validating values with SCHEMA Expand / Collapse
Author
Message
Posted Monday, July 16, 2007 5:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3120.asp

.
Post #382155
Posted Wednesday, August 1, 2007 11:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 4, 2012 6:07 AM
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 ....

 

Post #387130
Posted Wednesday, August 8, 2007 4:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

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)



.
Post #388856
Posted Wednesday, September 19, 2007 5:36 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:40 PM
Points: 612, Visits: 212

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.

Post #400567
Posted Wednesday, September 19, 2007 11:41 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 460, Visits: 637
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



Post #400646
Posted Wednesday, September 19, 2007 11:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

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



.
Post #400647
Posted Saturday, March 7, 2009 10:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 7:20 AM
Points: 11, Visits: 90
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.



Post #671060
Posted Monday, March 9, 2009 1:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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)



.
Post #671898
Posted Saturday, March 28, 2009 2:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 7:20 AM
Points: 11, Visits: 90
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





  Post Attachments 
xsd.txt (8 views, 6.82 KB)
Post #685527
Posted Saturday, March 28, 2009 11:54 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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/


.
Post #685599
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse