Professional SQL Server™ 2005 Integration Services

8.5. XML Data

SQL 2005 provides many ways to work with XML. The XML Data Source is yet another jewel in the SSIS treasure chest. It enables you to import an XML file directly into relational tables if that is what you need to do. In this example, you will import an RSS (Really Simple Syndication) file from the Web. You may not want to let your manager know how easy this is!

Create a new Integration Services project to get started. Add a Data Flow task to the Control Flow design area. Click the Data Flow tab to view the Data Flow design area. Add an XML Source and name it SQLNews. Double-click the SQLNews component to open the XML Source Editor. Make sure that Connections Manager is selected on the left. Select XML File Location for the Data Access Mode. For the XML location property, type in the following address:

http://www.sqlservercentral.com/sscrss.xml

If you click the Browse button, a regular file open dialog box opens. It is not obvious at first that you can use a URL address instead of a file on disk.

The XML file must be defined with an XML Schema Definition (XSD), which describes the elements in the XML file. Some XML files have an in-line XSD, which you can determine by opening the file and looking for xsd tags. There are many resources and tutorials available on the Web if you would like to learn more about XML schemas. If the file you are importing has an in-line schema, make sure that "Use inline schema" is checked. If an XSD file is available, you can enter the path ...