SQL Server/T-SQL Tutorial/XML/XML Schema

Using XML with Open Schema

   <source lang="sql">

3> 4> CREATE TABLE dbo.Contacts 5> ( 6> contactid INT NOT NULL PRIMARY KEY, 7> contactname NVARCHAR(50) NOT NULL, 8> I18N BIT NOT NULL, 9> otherattributes XML NOT NULL 10> ); 11> GO 1> 2> CREATE TABLE dbo.I18N 3> ( 4> ID NVARCHAR(15), 5> FL NVARCHAR(50) 6> ); 7> 8> CREATE TABLE dbo.Foreigns 9> ( 10> NL NVARCHAR(50), 11> ENG BIT 12> ); 13> GO 1> 2> -- Store the Schemas in a Variable and Create the Collection 3> DECLARE @mySchema NVARCHAR(MAX); 4> 5> SET @mySchema = N""; 6> 7> SET @mySchema = @mySchema + 8> (SELECT * 9> FROM I18N 10> FOR XML AUTO, ELEMENTS, XMLSCHEMA("I18N")); 11> 12> SET @mySchema = @mySchema + 13> (SELECT * 14> FROM Foreigns 15> FOR XML AUTO, ELEMENTS, XMLSCHEMA("Foreign")); 16> 17> -- Create Schema Collection 18> CREATE XML SCHEMA COLLECTION dbo.ContactOtherAttributes AS @mySchema; 19> GO 1> 2> -- Drop Tables 3> DROP TABLE dbo.I18N, dbo.Foreigns, Contacts; 4></source>