As part of my job, I have to load data from various data sources into SQL Server. I recently had to load data from a well defined XML export into a SQL Server 2008 Express R2 instance. This is made a little more difficult than usual as the Express version of SQL Server does not include SSIS. It can be accomplished, but you need a few things installed first.
To accomplish this task, the following tools were used:
- Microsoft SQLXML
- Microsoft SOAP Toolkit if the SQLXML installer tells you that you need it.
- SQL Server 2008 R2 Express
Once you have the tools installed, copy and paste the xml below into a text editor and save it as C:\Booklist.xml
<?xml version="1.0" ?> <Library> <Books> <Title>Mr. Magoo's Big Adventure</Title> <Author>Mr. Magoo</Author> <Volume>1</Volume> <PublishDate>2002-05-30T09:30:10-06:00</PublishDate> </Books> <Books> <Title>The FooBar Chronicles</Title> <Author>The Bug Master</Author> <Volume>1</Volume> <PublishDate>2010-05-30T09:30:10-06:00</PublishDate> </Books> <Books> <Title>Adventures in SQL Land</Title> <Author>Commander Data</Author> <Volume>1</Volume> <PublishDate>2009-05-30T09:30:10-06:00</PublishDate> </Books> </Library>
Now that we have an XML file to import, we need a table to import it to. Copy and paste the following query into your SQL Server Management Studio window and execute it.
CREATE TABLE Booklist ( Title varchar(25) ,Author varchar(14) ,Volume int ,PublicationDate DateTime )
Once we have somewhere to put the data, we need to tell SQLXML how to put it there. Copy and paste the following XSD into a text editor and save it to C:\Booklist.xsd
<?xml version="1.0" standalone="no" ?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:element name="Books" sql:relation="Booklist" type="Books_type"> <xsd:complexType name="Books_type"> <xsd:element name="Title" sql:field="Title" type="xsd:string"/> <xsd:element name="Author" sql:field="Author" type="xsd:string"/> <xsd:element name="Volume" sql:field="Volume" type="xsd:integer"/> <xsd:element name="PublishDate" sql:field="PublicationDate" type="xsd:dateTime" sql:datatype="dateTime"/> </xsd:complexType> </xsd:element> </xsd:schema>
The sql:relation attribute in the Books element defines which table the data will be loaded into. The value of this attribute must match the table name in SQL Server. The sql:field attribute in the Title, Author, Volume, and PublishDate elements defines which column in the SQL Server table the data will be placed into. Note that it is not necessary to have the sql:relation and sql:field attributes be the same as the name attribute of the element. This allows you to map the data to a completely different structure than the original XML file. Also note, that the PublishDate element has an additional attribute sql:datatype. The sql:datatype tells the XML Bulk Loader what SQL Server datatype it should use when converting the incoming data.
Lastly, we need a script to connect to the database and call the XML Bulk Loader. Copy and paste the following code into a text editor and save it to C:\ImportBooklist.vbs
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBL.ConnectionString = "Provider=SQLOLEDB;Data Source=HostName\InstanceName;Initial Catalog=DatabaseName;IntegratedSecurity=SSPI" objBL.ErrorLogFile = "C:\error.log" objBL.Execute "C:\Booklist.xsd", "C:\Booklist.XML" set objBL = Nothing
Change the HostName to the name of the computer hosting your SQL Server Express instance. Change the InstanceName to the name of the SQL Server Express instance hosting your database. Finally change the DatabaseName to the name of the database that holds the table where you want to put the data. The connection string provided above uses Windows Authentication to connect to the database. If you wanted to use standard SQL Server Authentication, your connection string would be:
objBL.ConnectionString = "Provider=SQLOLEDB;Data Source=HostName\InstanceName;Initial Catalog=DatabaseName;User Id=myUsername;Password=myPassword"
Once you have made your edits, you should be able to double-click your C:\ImportBooklist.vbs file and the data will load into the table specified.
A few of the links that I found helpful:
Hope this quick example helps someone.