Create an XML Schema collection in SQL Server 2012?

Question

I want to create an XML schema collection to validate some XML in a column for the following XML:

DECLARE @xml as xml
SET @xml='
<R C="firstName" O="" N="Fred" />
 <R C="lastName" O="" N="Bloggs" />
 <R C="emailAddress" O="" N="[email protected]" />
 <R C="phone" O="" N="" />
 <R C="mobile" O="" N="" />
 <R C="fax" O="" N="" />
 <R C="OrganisationId" O="" N="286" />
'
SELECT AuditDetail.Col.value('(@C)[1]','nvarchar(80)') DatabaseColumnName,
 AuditDetail.Col.value('(@O)[1]','nvarchar(MAX)') OldValue,
 AuditDetail.Col.value('(@N)[1]','nvarchar(MAX)') NewValue
 FROM @xml.nodes('/R') AS [AuditDetail](Col)

The validation should be that D is nvarchar(80) O is nvarchar(MAX) N is nvarchar(MAX)

Can someone show me what the schema collection script would look like?

Answer 1

The scheme could look like this.

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="R">
 <xs:complexType>
 <xs:attribute name="C">
 <xs:simpleType>
 <xs:restriction base="xs:string">
 <xs:maxLength value="80"/>
 </xs:restriction>
 </xs:simpleType>
 </xs:attribute>
 <xs:attribute name="O" type="xs:string"/>
 <xs:attribute name="N" type="xs:string"/>
 </xs:complexType>
 </xs:element>
</xs:schema>

It defines a single element R with the attributes C, O and N where C is restricted to 80 characters.

A valid XML document according to that schema would contain only one element R as the root node.

What you have here is a XML fragment and SQL Server can handle those just fine. Actually, the default behaviour for the XML datatype is that it will allow XML fragments.

Create the schema:

create xml schema collection dbo.R as 
'<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="R">
 <xs:complexType>
 <xs:attribute name="C">
 <xs:simpleType>
 <xs:restriction base="xs:string">
 <xs:maxLength value="80"/>
 </xs:restriction>
 </xs:simpleType>
 </xs:attribute>
 <xs:attribute name="O" type="xs:string"/>
 <xs:attribute name="N" type="xs:string"/>
 </xs:complexType>
 </xs:element>
</xs:schema>
';

Test:

declare @xml as xml(dbo.R);
set @xml='
 <R C="firstName" O="" N="Fred" />
 <R C="lastName" O="" N="Bloggs" />
 <R C="emailAddress" O="" N="[email protected]" />
 <R C="phone" O="" N="" />
 <R C="mobile" O="" N="" />
 <R C="fax" O="" N="" />
 <R C="OrganisationId" O="" N="286" />
';
select @xml;

If you want to change the XML to only validate XML documents you need to specify document in the variable declaration.

declare @xml as xml(document dbo.R);