This topic illustrates how to assign a schema to a field that is natively defined as XML type in the database. The instructions below use SQL Server 2014 and the AdventureWorks 2014 database. The latter can be downloaded from the AdventureWorks samples page on GitHub (https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks). Note that mapping of data to or from XML fields works in the same way with other database types that support XML fields.
To add the Adventure Works 2014 database as a mapping component:
1.On the Insert menu, click Database, and follow the wizard to connect to the database using your preferred method (ADO or ODBC). For more information, see Connecting to Microsoft SQL Server (ADO) and Connecting to Microsoft SQL Server (ODBC). NOTE: If you use the SQL Server Native Client driver, you might need to set the Integrated Security property to a space character (see Setting up the SQL Server Data Link Properties ).
2. On the Insert Database Object dialog box, expand the Production schema, and then select the ProductModel table.
The database table has now been added to the mapping area. Notice that this table has two fields of XML type: CatalogDescription and Instructions:
For the structure of the XML fields to appear on the mapping, the XML schema of the field content is required. Right-click the Instructions field and select Assign XML Schema to Field from the context menu.
In this particular example, you will assign a schema to the Instructions field directly from the database. To do this, select the Production.ManuInstructionsSchemaCollection item next to the Database option, and then click OK.
The structure of the XML field now appears on the component. You can now draw connections (and map data) to or from this field.