Connecting to Microsoft SQL Server (ODBC)
This example illustrates how to connect to a SQL Server database through ODBC.
•Download and install the Microsoft ODBC Driver for SQL Server from the Microsoft website, see https://docs.microsoft.com/en-us/SQL/connect/odbc/download-odbc-driver-for-sql-server. This example uses Microsoft ODBC Driver 17 for SQL Server to connect to a SQL Server 2016 database. You might want to download a different ODBC driver version, depending on the version of SQL Server where you want to connect. For information about ODBC driver versions supported by your SQL Server database, refer to the driver's system requirements.
To connect to SQL Server using ODBC:
1.Start the database connection wizard.
2.Click ODBC Connections.
3.Select User DSN (or System DSN, if you have administrative privileges), and then click Add .
4.Select the driver from the list. Note that the driver appears in the list only after it has been installed.
5.Click User DSN (or System DSN if you are creating a System DSN).
Creating a System DSN requires that XMLSpy be run as an administrator. Therefore, in order to create a System DSN, cancel the wizard, make sure that you run XMLSpy as an administrator, and perform the steps above again.
6.Enter a name and, optionally, a description to identify this connection, and then select from the list the SQL Server to which you are connecting (SQLSERV01 in this example).
7.If the database server was configured to allow connections from users authenticated on the Windows domain, select With Integrated Windows authentication. Otherwise, select one of the other options, as applicable. This example uses With SQL Server authentication... , which requires that the user name and password be entered in the relevant boxes.
8.Optionally, select the Change the default database to check box and enter the name of the database to which you are connecting (in this example, Sandbox).
9.Click Next and, optionally, configure additional parameters for this connection.
10.Click Finish. A confirmation dialog box listing the connection details opens.
11.Click OK. The data source now appears in the list of User or System data sources, as configured, for example: