The XML via SQL Data Provider loads the data from a hierarchical XML document into a relational schema. This schema can be queried via SQL like a relational database.
Figure 6-1: XML via SQL Data Provider
Using the built-in SQL-based XML Port Manager, you can define one or more output data streams. To validate the XML source file, you can provide either a XML Schema file or a DTD description file.
You do not need to understand XML in detail to use this component. It is useful for data-centric XML documents (such as, sales order, stock quotes, scientific data) that are characterized by a regular hierarchical structure. Use this component to represent the data from the XML document in a relational table structure for further data transformation.
Select the XML Source file. This can be an HTTP, FTP, URL, or a file name.
Click the XML button of the Data Output property to open the XML Port Manager.
Specify SELECT statements based on the table structure created from the XML document for each output port (see description of the XML Port Manager below). There is one output port per default, but you can add ports. To create the SELECT statement, you can use the Query Designer.
To enter required properties