July 16, 2007 at 5:33 pm
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3120.asp
.
August 1, 2007 at 11:45 am
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 ....
August 8, 2007 at 4:33 am
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)
.
September 19, 2007 at 5:36 pm
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.
September 19, 2007 at 11:41 pm
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
September 19, 2007 at 11:57 pm
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
.
March 7, 2009 at 10:12 pm
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.
March 9, 2009 at 1:04 pm
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)
.
March 28, 2009 at 2:34 am
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
March 28, 2009 at 11:54 am
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/
.
April 2, 2009 at 10:02 pm
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 XML
AS
BEGIN
BEGIN TRY
DECLARE @EmpXML AS XML(EmployeeSchema) --declaring a local XML variable and binding it to a schema
DECLARE @LocalError
SET @EmpXML = @EmployeeXML -- here I am setting the @EmpXML to @EmployeeXML variable passed in
IF @@error<>0 --if the @EmployeeXML failed the XSD validation, i beileve there would be an error right
BEGIN
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 here
END TRY
BEGIN CATCH
--I have another another user defined errorhandler proc here which will take the above @LocalError
--as input parameter and raise a detailed error
END CATCH
END --End of Proc
May 5, 2009 at 1:41 am
Here is an example that I just tried and it works
DECLARE @x XML(CustomerSchema), @y XML
SELECT @y = 'a'
BEGIN TRY
SELECT @x = @y
END TRY
BEGIN CATCH
RAISERROR('Hey, this is an invalid XML',16,1)
END CATCH
/*
OUTPUT:
Msg 50000, Level 16, State 1, Line 7
Hey, this is an invalid XML
*/
.
July 25, 2011 at 8:32 am
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.
January 28, 2013 at 11:56 am
Is there any way to get all error messages validating xml using xsd?
no CLR or any dlls.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply