Using XML with Open Schema : XML Schema « XML « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. XML
3. XML Schema

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>