How to valide a XML with XSD in SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to valide a XML with XSD in SQL

Author

Topic

Mathias
Posting Yak Master

119 Posts

Posted - 2008-10-14 : 04:32:35

Is it possible to validate an XML created with select... FOR XML against an XSD?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 05:04:17

one way to do this is to create a variable with xml datatype and define schema collection for it and then putting the query result xml to column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 05:38:04

Visakh16, I think OP means the other way.
The data is already stored and OP wants to validate the SELECT resultset.

WITH XMLNAMESPACES ('uri1' as ns1, 
                    'uri2' as ns2,
                    DEFAULT 'uri2')
SELECT ProductID, 
      Name,
      Color
FROM Production.Product 
WHERE ProductID=316 or ProductID=317
FOR XML RAW ('ns1:Product'), ROOT('ns2:root'), ELEMENTS

E 12°55'05.63"
N 56°04'39.26"

Mathias
Posting Yak Master

119 Posts

Posted - 2008-10-14 : 09:00:36

I have found an example but it is not with an xsd (this is what I need) :
CREATE XML SCHEMA COLLECTION SC AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://ns">
<element name="e" type="anyType"/>
<element name="a" type="byte"/>
<element name="b" type="string"/>
</schema>'
go

The following example succeeds, because the strict validation of <e> is successful:

DECLARE @var XML(SC)
SET @var = '<e xmlns="http://ns"><a>1</a><b>data</b></e>'
go

The following example fails. The instance is rejected, because the strict validation of element e does not find element c defined in the schema:

DECLARE @var XML(SC)
SET @var = '<e xmlns="http://ns"><a>1</a><c>Wrong</c><b>data</e>'
go

Again, the XML instance in the following example is rejected, because there is no declaration for element c in namespace http://whatever. In other words, the namespace does not exist.

DECLARE @var XML(SC)
SET @var = '<e xmlns="http://ns"><a>1</a><c xmlns="http://whatever">Wrong</c><b>data</e>'
SELECT @var
go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources