Introduction

Transferring data between partner companies is pretty routine, and XML is one of the common formats that data are transferred in. One of the more common destinations for the XML data is a SQL Server database. In the past, XML was difficult to work with, but the .NET framework improved XML parsing greatly. Linq to XML has made XML handling absolutely painless, and Linq to SQL makes entering the data just as easy.

This example isn’t meant to say this is the only way to transform XML into SQL, because like all things .NET, there are several ways to do this. This method may or may not work depending on your circumstances.

Getting Started

The first thing you need is a sample XML document and a database to receive the data. It’s a bad idea to transform data directly into an application database or data warehouse. Instead, you should enter the data into a receiving database and then transform the data to its final destination using SSIS or an application function. In this example, we’ll transform our data into a database which resembles the XML structure, and assume there is another process which will import the data into our ERP system. The sample XML and table creation scripts will be included in the download accompanying this article. The sample XML should be placed outside of your project (such as c:\xml\) so it’s easier to find when we run the code.

We use the sample XML to create an XML Schema Document (XSD). When an XSD is present in a project, we have full Intellisense and some nice syntactic shortcuts to speed our development. To create an XSD, open the sample XML file in Visual Studio, then click XML >> Create Schema. An XML schema will be created. Be sure to save the XSD in your project folder, then refresh the file list in your project and include the XSD.

Figure 1: Creating an XML Schema

So far, so good. We now have an XML schema and XML Intellisense, and now it’s time to describe our database. If you haven’t done so already, create a new SQL Server database and add the two tables in the example project. Add a new Linq to SQL Class to your project, and connect to the database which will receive the XML data via the Server Explorer. Drag the orders and items tables to the Linq canvas.

Figure 2: Order and Item tables on the Linq canvas.

In real life, the XML schemas and database structures will be more complex, but we have enough structure to give you a good idea of what you need to do.

Coding the Transformation

This is where the fun begins! Linq to XML in VB.NET introduced a great deal of new functionality in the handling of XML data. When combined with the Intellisense provided by the XSD we created, we can directly address XML elements as collections or properties of an XML object. To see this in action, start by loading our XML document (make sure to put in the correct path on your machine). Use the following code to load the XML document:

Now, we’re ready to start mapping from XML into SQL Server. We need to loop through each order in the XML file, so we need to obtain a collection of orders from the XML document. In the past, we might use an XPath query or loop through order elements, but in Linq, we can obtain a collection of orders simply by assigning all of the order elements to another object. As you start typing the following line of code, you’ll see new Intellisense hints, the first two of which relate to directly addressing XML elements (Figure 3).

Figure 3: Intellisense hints in Linq to XML

Figure 4: XML Intellisense

One major difference you’ll notice in this code is that we didn’t declare a type for _orders. Instead, we used another new feature of VB.NET called Anonymous Types. An anonymous type is one whose exact type is determined at compile time. Because Visual Studio 2008 compiles code in the background as the code is written, we have immediate strong typing and Intellisense. The compiler relies on our XSD file to determine the XML’s structure. Anonymous Types are different from the VAR data type, which has no particular type—anonymous types are strongly typed.

Now that we have our collection, we can use another anonymously typed variable in a for each loop:

Once again, we don’t have to declare a specific type for _order—the compiler will determine the correct type for us.

Now, inside of this loop is where we start sending our data to the database. First thing we need is a local variable of the type we want to work with—in this case, the order class from the database context.

Once we have this object, we can begin assigning values to it properties. Start typing the code shown below, and notice again that we have Intellisense for both the members of _order including attributes, specified by @, and the child elements. The OrderNumber in our database corresponds to the order number attribute in our XML file, so we’ll assign order number to OrderNumber.

Figure 5: Intellisense for data context types

Figure 6: Choosing attributes via intellisense for anonymous types

Figure 7: Intellisense for the attributes themselves

We’ll repeat this process for the other values we need to populate, using values from the other elements in our xml order segments. Begin typing the following line of code, and you’ll see the now familiar Intellisense. Also, notice that we have been directly entering XML as part of the VB syntax. This is a major change in .NET 3.5—that than parsing and querying for values, XML is a first class member of VB.NET.

Figure 8: Choosing elements via Intellisense

Figure 9: Intellisense for the elements themselves

Let’s finish with the rest of the order-level data. The entire loop should read as follows:

So far, so good. We’ve assigned values to properties. But, the values won’t yet be written to the database. Linq to SQL has an interesting ability, which allows us to queue changes to the database before writing the changes (similar in concept to an in-memory dataset). This is a function of the data context, and uses the InsertOnSubmit method. All we need to do is add our orders to the queue, and execute the update command. The pseudocode for queuing changes is

Put in the context of our example, the loop should now read:

We can either write the changes after each pass of the loop, or write all the changes at once—it’s a choice you’ll probably make on a project-by-project basis. We use the SubmitChanges() method to write the changes, and we’ll write all the changes at once, so the command will be placed outside of our loop. Our entire method should now read:

At this point, you can build and run our project, and two orders should show up in your database. Orders are great, but we also need to know the items on each order. And, we need to make sure that the items are associated with the correct order. Remember that we related the two tables based on the OrderId, not the OrderNumber. In the past, this meant we’d have to perform an insert, get the scope_identity(), then perform the second insert. Today, Linq to SQL handles all of that for us!

Each order has a collection of items, and just like the XML file, our Linq-to-XML order object has a collection associated with it. This means we can loop through each order’s items inside our orders loop. Just like with orders, we can access the items using simple XML syntax, complete with Intellisense.

To see this in action, modify your code as shown below. Because we related orders and items in our database, with orders being the primary key table, our Linq-to-SQL datacontext honors this relationship, and creates a collection of items in the order object, with an Add method we use to add items to the order. As before, changes are committed to the database when we call the InsertOnSubmit method of the order object. Because the items collection is part of the order object, they are inserted in the same database transaction as the order itself. Identities and relations are automatically maintained by Linq.

At this time, we can run our project and check our database—the orders and items should be in their respective tables.

Summary

Visual Basic 9, released with Visual Studio 2008, brought about a number of improvements, including Linq and XML as a first class member of the language. These improvements enable rapid development of complicated scenarios which were significantly more difficult than even the previous version, which itself was hailed as greatly simplifying the same development patterns. This article demonstrates a common integration scenario—the conversion of XML data into SQL Server tables—and how easily it can be achieved.

About Rich Dudley

Sorry, no bio is available View complete profile