Create an XML Schema collection in SQL Server 2012? 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='

&lt;R C="firstName" O="" N="Fred" /&gt;
    &lt;R C="lastName" O="" N="Bloggs" /&gt;
    &lt;R C="emailAddress" O="" N="FredBloggs@gmail.com" /&gt;
    &lt;R C="phone" O="" N="" /&gt;
    &lt;R C="mobile" O="" N="" /&gt;
    &lt;R C="fax" O="" N="" /&gt;
    &lt;R C="OrganisationId" O="" N="286" /&gt;

'

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?
The scheme could look like this.

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


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


Test:

declare @xml as xml(dbo.R);

set @xml='
  &lt;R C="firstName" O="" N="Fred" /&gt;
  &lt;R C="lastName" O="" N="Bloggs" /&gt;
  &lt;R C="emailAddress" O="" N="FredBloggs@gmail.com" /&gt;
  &lt;R C="phone" O="" N="" /&gt;
  &lt;R C="mobile" O="" N="" /&gt;
  &lt;R C="fax" O="" N="" /&gt;
  &lt;R C="OrganisationId" O="" N="286" /&gt;
';

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