Sql-server – Create an XML Schema collection in SQL Server 2012

sql-server-2012xml

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="FredBloggs@gmail.com" />
    <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?

Best Answer

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="FredBloggs@gmail.com" />
  <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);
Related Question