Importing an XML File

How can I import an XML file into SQL Server 2000?

You can import an XML file into SQL Server in several ways. Here are three approaches.

If you want to extract the document's entities and attributes into relational tables, the fastest way to import XML into SQL Server is to use the Bulk Load COM interface in SQL Server 2000 Web Release (SQLXML) 3.0 Service Pack 1 (SP1). The interface comes with the free SQLXML download at http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/824/msdncompositedoc.xml. This method of importing XML is fast, and the download documents the Bulk Load tool with code samples.

If you don't want to extract the document's entities and attributes into relational tables, you can use the textcopy.exe command-line utility. Using textcopy.exe is a good method for loading the document into a text data type column.

If your XML document is simple, you can use Data Transformation Services (DTS) by writing transformation code that looks at each line of the XML document and extracts the information into tables. This approach requires you to define the XML file as an input data source, define a database table as the output data source, and write ActiveX script that parses the input for "" characters to extract entities, attributes, and their values.