SQLServerCentral / Discuss Content Posted by Jacob Sebastian / Article Discussions / Article Discussions by Author / XML Workshop VII - Validating values with SCHEMA / Latest Posts

RE: XML Workshop VII - Validating values with SCHEMA

Milko González — Mon, 28 Jan 2013 11:56:15 GMT

Is there any way to get all error messages validating xml using xsd?no CLR or any dlls.

RE: XML Workshop VII - Validating values with SCHEMA

poorna.saikam — Mon, 25 Jul 2011 08:32:11 GMT

hi, can any one please let me know how to get multiple error messages while validating xml using xsd?no CLR or any dlls.thank you so much in advance.

RE: XML Workshop VII - Validating values with SCHEMA

jacob sebastian — Tue, 05 May 2009 01:41:26 GMT

Here is an example that I just tried and it works[code]DECLARE @x XML(CustomerSchema), @y XMLSELECT @y = 'a'BEGIN TRY SELECT @x = @yEND TRYBEGIN CATCH RAISERROR('Hey, this is an invalid XML',16,1)END CATCH/*OUTPUT:Msg 50000, Level 16, State 1, Line 7Hey, this is an invalid XML*/[/code]

RE: XML Workshop VII - Validating values with SCHEMA

bdba — Thu, 02 Apr 2009 22:02:07 GMT

Hi Jacob,I am trying to Raise an error using RAISEERROR function in sql when the XML schema validation fails.I am doing this inside a store proc. I am declaring the XML doc binding it to it's XSD schema in the TRY block and if validation fails, raise a user define error number by using RAISEERROR function in the catch block. However, somehow i am not able to do this and sql return it's own error the moment it executes that line of code. So using Employee example here, I am doing something like this.CREATE PROC ImportEmployeeInfo@EmployeeXML as XMLASBEGINBEGIN TRYDECLARE @EmpXML AS XML(EmployeeSchema) --declaring a local XML variable and binding it to a schemaDECLARE @LocalErrorSET @EmpXML = @EmployeeXML -- here I am setting the @EmpXML to @EmployeeXML variable passed inIF @@error<>0 --if the @EmployeeXML failed the XSD validation, i beileve there would be an error rightBEGIN SET @LocalError = 50001 --user defined error RaiseError(' The input parameter @EmployeeXML is not valid', 16, 1)END--The XML shredding and import into table goes hereEND TRYBEGIN CATCH--I have another another user defined errorhandler proc here which will take the above @LocalError --as input parameter and raise a detailed errorEND CATCHEND --End of Proc

RE: XML Workshop VII - Validating values with SCHEMA

jacob sebastian — Sat, 28 Mar 2009 11:54:53 GMT

The regular expression language is documented here: http://www.w3.org/TR/xmlschema-2/#regexsThe 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/

RE: XML Workshop VII - Validating values with SCHEMA

tengtium — Sat, 28 Mar 2009 02:34:29 GMT

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.[code]Msg 102, Level 15, State 1, Line 32Incorrect syntax near 'year'.[/code]xsd collection attached

RE: XML Workshop VII - Validating values with SCHEMA

jacob sebastian — Mon, 09 Mar 2009 13:04:24 GMT

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:[code]DECLARE @x XML(DOCUMENT EmployeeSchema)[/code]

RE: XML Workshop VII - Validating values with SCHEMA

tengtium — Sat, 07 Mar 2009 22:12:13 GMT

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[code] DECLARE @emp AS XML(EmployeeSchema) SET @emp = ''[/code]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[code] DECLARE @emp AS XML(EmployeeSchema) SET @emp = 'put a valid xml comment here'[/code]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.

RE: XML Workshop VII - Validating values with SCHEMA

jacob sebastian — Wed, 19 Sep 2007 23:57:00 GMT

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

RE: XML Workshop VII - Validating values with SCHEMA

Mikael Eriksson SE — Wed, 19 Sep 2007 23:41:00 GMT

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

RE: XML Workshop VII - Validating values with SCHEMA

Harit Gohel — Wed, 19 Sep 2007 17:36:00 GMT

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.

RE: XML Workshop VII - Validating values with SCHEMA

jacob sebastian — Wed, 08 Aug 2007 04:33:00 GMT

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 = '

<FullName>Jacob</FullName>

</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)

RE: XML Workshop VII - Validating values with SCHEMA

dinesh sodani — Wed, 01 Aug 2007 11:45:00 GMT

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

XML Workshop VII - Validating values with SCHEMA

jacob sebastian — Mon, 16 Jul 2007 17:33:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3120.asp