Modifying an existing XML Schema collection

Question

I have schema collection association for an XML column. I want to add a child element to an existing parent in the schema collection.

I am trying to achieve this without dropping the schema association because the table having that column contains data and it restricts while dropping the association.

The schema looks like this

create xml schema collection dbo.Book
as
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Books">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element type="xsd:string" name="AuthorFirstName" maxOccurs="unbounded" minOccurs="0"/>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>';
go

Now I want to add one more child element inside the parent element(Books), like this

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Books">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element type="xsd:string" name="AuthorFirstName" maxOccurs="unbounded" minOccurs="0"/>
        <xsd:element type="xsd:string" name="AuthorLastName" maxOccurs="unbounded" minOccurs="0"/>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Any solutions to alter the XML schema collection without dropping it?

Be aware, from [SQL Books online](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-xml-schema-collection-transact-sql), "adding a new global element, type, or attribute declaration to the schema collection will cause a revalidation of all the stored data that is constrained by the schema collection." — AlwaysLearning, Sep 12 '19 at 12:11

score 1 · Accepted Answer · answered Sep 12 '19 at 13:18

SQL Server restricts you a little here. It won't allow you to modify existing elements in an existing namespace in an existing XML Schema Collection. If your schema namespaces are designed to be extensible, though, you can add more namespaces to an existing XML Schema Collection.

Considering the following, which defines an extensible "Books V1" namespace and then adds a new element via a "Books V2" namespace...

create xml schema collection dbo.Book as
N'<xs:schema targetNamespace="http://tempuri.org/Books-v1"
  xmlns="http://tempuri.org/Books-v1"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  elementFormDefault="qualified">
  <xs:element name="Books">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:string" name="AuthorFirstName" minOccurs="0" maxOccurs="unbounded"/>
        <xs:any namespace="##other" minOccurs="0" processContents="lax"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>';
go
alter xml schema collection dbo.Book add
N'<xs:schema targetNamespace="http://tempuri.org/Books-v2"
  xmlns="http://tempuri.org/Books-v2"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"  
  elementFormDefault="qualified">
  <xs:element type="xs:string" name="AuthorLastName"/>
</xs:schema>';
go
declare @Book1 xml(dbo.Book) =
N'<Books xmlns="http://tempuri.org/Books-v1">
  <AuthorFirstName>John</AuthorFirstName>
</Books>';
declare @Book2 xml(dbo.Book) =
N'<Books xmlns="http://tempuri.org/Books-v1" xmlns:v2="http://tempuri.org/Books-v2">
  <AuthorFirstName>John</AuthorFirstName>
  <v2:AuthorLastName>Smith</v2:AuthorLastName>
</Books>';
select @Book1;
select @Book2;
go
drop xml schema collection dbo.Book;
go

Modifying an existing XML Schema collection

1 Answers1