2

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?

1 Answer 1

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.