Adding Dynamic Content to an XML Document

The XML documents we’ve built in our code so far were static. Because XML support is built into VB, you can also create dynamic context, and this is where things get quite interesting. To insert some dynamic content into an XML document, insert the characters <%=. The editor will automatically insert the closing tag, which is %>. Everything within these two tags is treated as VB code and compiled. The two special tags create a placeholder in the document (or an expression hole), and the expression you insert in them is an embedded expression: You embed a VB expression in your document, and the compiler evaluates the expression and inserts the result in the XML document.

Here’s a trivial XML document with an embedded expression. It’s the statement that creates a Books document with a Book element (I copied it from a code segment presented earlier in this chapter), and I inserted the current date as an element:

Dim doc = _
		New XElement("Books", _
		New XElement("Book", _
		New XAttribute("ISBN", "0000000000001"), _
		New XAttribute("RecordDate", <%= Today %>), _
		New XElement("Price", 11.95), _
		New XElement("Name", "Book Title 1"), _
		New XElement("Stock", _
			New XAttribute("InStock", 12), _
			New XAttribute("OnOrder", 24))),Code language: PHP (php)

Let’s say you have an array of Product objects and you want to create an XML document with these objects. Listing 13.1 shows the array with the product names.

Listing 13.1: An Array of Product Objects

Dim Products() As Product = _
		{New Product With _
			{.ProductID = 3, .Pr	oductName = "Product A", _
			.ProductPrice = 8.75, _
			.ProductExpDate = #2/2/2009#}, _
		New Product With _
			{.ProductID = 4, .ProductName = "Product B",  _
			.ProductPrice = 19.5}, _
		New Product With _
			{.ProductID = 5, .ProductName = "Product C", _
			.ProductPrice = 21.25, _
			.ProductExpDate = #12/31/2010#}}Code language: PHP (php)

The code for generating an XML document with three elements is quite short, but what if you had thousands of products? Let’s assume that the Products array contains instances of the Product class. You can use the XMLSerializer class to generate an XML document with the array’s contents. An alternative approach is to create an inline XML document with embedded expressions, as shown in Listing 17.2.

Listing 17.2: An XML Document with Product Objects

Dim prods = <Products>
		<%= From prod In Products _
		Select <Product>
			<ID><%= prod.ProductID %></ID>
			<Name><%= prod.ProductName %></Name>
			<Price><%= prod.ProductPrice %></Price>
			<ExpirationDate>
				<%= prod.ProductExpDate %></ExpirationDate>
			</Product> %>
		</Products>Code language: JavaScript (javascript)

This code segment looks pretty ugly, but here’s how it works: In the first line, we start a new XML document. (The prods variable is actually of the XElement type, but an XElement is in its own right an XML document.) Notice that there’s no line-continuation character at the end of the first line of the XML document. Then comes a LINQ query embedded in the XML document with the <%= and %> tags. Notice the line continuation symbol at the end of this line. When we’re in an expression hole, we’re writing VB code, so line breaks matter. That makes the line continuation symbol necessary. Here’s a much simplified version of the same code:

Dim prods = <Products>
		<%= From prod In Products _
			Select <Product>some product</Product> %>
		</Products>Code language: JavaScript (javascript)

This code segment will generate the following XML document:

<Products>
	<Product>some product</Product>
	<Product>some product</Product>
	<Product>some product</Product>
</Products>Code language: HTML, XML (xml)

The file contains no real data but is a valid XML document. The two tags with the percent sign switch into VB code, and the compiler executes the statements embedded in them. The embedded statement of our example is a LINQ query, which iterates through the elements of the Products array and selects literals (the XML tags shown in the output). To insert data between the tags, we must switch to VB again and insert the values we want to appear in the XML document. In other words, we must replace the string some product in the listing with some embedded expressions that return the values you want to insert in the XML document. These values are the properties of the Product class, as shown in Listing 17.1. The code shown in Listing 13.2 will produce the output shown in Listing 13.3.

Listing 13.3: An XML Document with the Data of the Array Initialized in Listing 13.2

<Products>
	<Product>
		<ID>3</ID>
		<Name>Product A</Name>
		<Price>8.75</Price>
		<ExpirationDate>2009-02-02T00:00:00</ExpirationDate>
	</Product>
	
	<Product>
		<ID>4</ID>
		<Name>Product B</Name>
		<Price>19.5</Price>
		<ExpirationDate>0001-01-01T00:00:00</ExpirationDate>
	</Product>
	
	<Product>
		<ID>5</ID>
		<Name>Product C</Name>
		<Price>21.25</Price>
		<ExpirationDate>2010-12-31T00:00:00</ExpirationDate>
	</Product>
</Products>Code language: HTML, XML (xml)

Transforming XML Documents

A common operation is the transformation of an XML document. If you have worked with XML in the past, you already know Extensible Stylesheet Language Transformations (XSLT), which is a language for transforming XML documents. If you’re new to XML, you’ll probably find it easier to transform XML documents with the LINQ to XML component. Even if you’re familiar with XSLT, you should be aware that transforming XML documents with LINQ is straightforward. The idea is to create an inline XML document that contains HTML tags and an embedded LINQ query, like the following:

Dim HTML = <html><b>Products</b>
		<table border="all"><tr>
		<td>Product</td><td>Price</td>
		<td>Expiration</td></tr>
		<%= From item In prods.Descendants("Product") _
			Select <tr><td><%= item.<Name> %></td>
				<td><%= item.<Price> %></td>
				<td><%= Convert.ToDateTime( _
				item.<ExpirationDate>.Value). _
					ToShortDateString %>
		</td></tr> %></table>
		</html>
HTML.Save("Products.html")
Process.Start("Products.html")Code language: PHP (php)

The HTML variable stores plain HTML code. HTML is a subset of XML, and the editor will treat it like XML: It will insert the closing tags for you and will not let you nest tags in the wrong order. The Select keyword in the query is followed by a mix of HTML tags and embedded holes for inline expressions, which are the fields of the item object. Note the VB code for formatting the date in the last inline expression.

The last two statements save the HTML file generated by our code and then open it in Internet Explorer (or whichever application you’ve designated to handle by default the HTML documents).

Dim hotels = <html>
	<table><tr><td>Hotel</td<td>Room Type</td><td>Price</td></tr>
		<%= From hotel In Hotels _
			Select <tr><td><%= hotel.<HotelName>.Value %></td>
			<td><%= GetRoomType(hotel.<RoomTypeID>)</td>
			<td><%= CalculatePrice(hotel.<Base>)</td>
			</tr>
		%>
	</table>
</html>

Working with XML Files

In this section, we’re going to build a functional interface for viewing customers and orders. And this time we aren’t going to work with a small sample file. We’ll actually get our data from one of the sample databases that come with SQL Server: the Northwind database. The structure of this database is discussed in Chapter, ‘‘Basic Concepts of Relational Databases,” in detail, but for now I’ll show you how to extract data in XML format from SQL Server. If you don’t have SQL Server installed, or if you’re unfamiliar with databases, you can use the sample XML files in the folder of the VBLINQ project. The frmXMLFiles form of the VBLINQ project is shown in Figure 13.2.

You may be wondering why you would extract relational data and process them with LINQ instead of executing SQL statements against the database. XML is the standard data- exchange format, and you may get data from any other source in this format. You may get an XML file generated from someone’s database, or even an Excel spreadsheet. In the past, you had to convert the data to another, more flexible format and then process it. With LINQ, you can directly query the XML document, transform it into other formats, and of course save it.

Start SQL Server and execute the following query:

SELECT * FROM Customers FOR XML AUTO

This statement selects all columns and all rows for the Customers table and generates an element for each row. The field values are stored in the document as attributes of the corresponding row. The output of this statement is not a valid XML document because its elements are not embedded in a root element. To request an XML document in which all elements are embedded in a root element, use the ROOT keyword:

SELECT * FROM Customers FOR XML AUTO, ROOT('AllCustomers')Code language: JavaScript (javascript)

I’m using the root element AllCustomers because the elements of the XML document are named after the table. The preceding statement will generate an XML document with the following structure:

<AllCustomers>
	<Customers CustomerID="..." CompanyName="aaa" ... />
	<Customers CustomerID="..." CompanyName="aaa" ... />
	...
</AllCustomers>Code language: HTML, XML (xml)

It would make more sense to generate an XML document with the Customers root element and name the individual elements Customer. To generate this structure, use the following statement:

SELECT * FROM Customers Customer FOR XML AUTO, ROOT('Customers')Code language: JavaScript (javascript)

Here’s a segment of the XML document with the customers:

<Customers>
	<Customer CustomerID="ALFKI" CompanyName=
		"Alfreds Futterkiste" ContactName="Maria Anders"
		ContactTitle="Sales Representative"
		Country="Germany" />
	<Customer CustomerID="ANATR" CompanyName=
		"Ana Trujillo Emparedados y helados"
		ContactName="Ana Trujillo" ContactTitle="Owner"
		Country="Mexico" />Code language: HTML, XML (xml)

Finally, you can create an XML document where the fields are inserted as elements, rather than attributes. To do so, use the ELEMENTS keyword:

SELECT * FROM Customers Customer FOR XML AUTO,
	ELEMENTS ROOT('Customers')Code language: JavaScript (javascript)

The other statements that generated the XML files with the rows of the tables Orders, Order Details, and Products are as follows:

SELECT * FROM Orders Order FOR XML AUTO, ROOT('Orders')
SELECT * FROM [Order Details] Detail FOR XML AUTO,
	ELEMENTS, ROOT('Details')
SELECT ProductID, ProductName FROM Products
	FOR XML AUTO, ELEMENTS ROOT('Products')Code language: JavaScript (javascript)

Notice that all files are attribute based, except for the Details.xml file, which is element based. I had no specific reason for choosing this structure; I just wanted to demonstrate both styles for processing XML in the sample project’s code. Also, the reason I’ve included the Products table is because the Order Details table, which contains the lines of the order, stores the IDs of the products, not the product names. When displaying orders, as shown in Figure 17.2, we must show product names, not just product IDs. The four collections with the entities we extracted from the Northwind database are declared and populated at the form’s level via the following statements:

Dim customers As XElement = XElement.Load("..\..\..\Customers.xml")
Dim orders As XElement = XElement.Load("..\..\..\Orders.xml")
Dim details As XElement = XElement.Load("..\..\..\Details.xml")
Dim products As XElement = XElement.Load("..\..\..\Products.xml")Code language: PHP (php)

As it’s apparent from the code, I’ve placed the four XML files created with the SQL statements shown earlier in the project’s folder. The Display Data button populates the top ListView control with the rows of the Customers table, via the following statements:

Private Sub bttnShow_Click(...) Handles bttnShow.Click
	For Each c In customers.Descendants("Customer")
		Dim LI As New ListViewItem
		LI.Text = c.@CustomerID
		LI.SubItems.Add(c.@CompanyName)
		LI.SubItems.Add(c.@ContactName)
		LI.SubItems.Add(c.@ContactTitle)
		ListView1.Items.Add(LI)
	Next
End SubCode language: PHP (php)

The code is quite simple. It doesn’t even use LINQ; it iterates through the Customer elements of the customers collection and displays their attributes on the control. Notice the use of the shortcut for the Attribute property of the current XElement.

When the user clicks a customer name, the control’s SelectedIndexChanged event is fired. The code in this handler executes a LINQ statement that selects the rows of the Orders table that correspond to the ID of the selected customer. Then, it iterates through the selected rows, which are the orders of the current customer, and displays their fields on the second ListView control via the following statements:

Private Sub ListView1 SelectedIndexChanged(...) _
		Handles ListView1.SelectedIndexChanged
	If ListView1.SelectedItems.Count = 0 Then Exit Sub
	ListView2.Items.Clear()
	Dim scustomerID = ListView1.SelectedItems(0).Text
	Dim query = From o In orders.Descendants("Order") _
			Where Convert.ToString(o.@CustomerID) = scustomerID _
			Select o
	For Each o In query
		Dim LI As New ListViewItem
		LI.Text = [email protected]
		LI.SubItems.Add(Convert.ToDateTime _
				(o.@OrderDate).ToShortDateString)
		LI.SubItems.Add(Convert.ToDecimal _
				(o.@Freight).ToString("#,###.00"))
		LI.SubItems.Add([email protected])
		ListView2.Items.Add(LI)
	Next
End SubCode language: PHP (php)

The LINQ query selects Order elements based on their CustomerID attribute. Finally, when an order is clicked, the following LINQ query retrieves the selected order’s details:

Dim query = From itm In details.Descendants("Detail") _
		Where Convert.ToInt32(itm.<OrderID>.Value) = orderID _
		Select itmCode language: HTML, XML (xml)

The Details.xml file contains elements for all columns, not attributes, and I use statements such as dtl.<UnitPrice> to access the subelements of the current element. To display product names, the code selects the row of the Products collection that corresponds to the ID of each detail line as follows:

Dim product = _
		From p In products.Descendants("Product") _
		Where Convert.ToInt32(p.@ProductID) = _
			Convert.ToInt32(dtl.<ProductID>.Value) _
		Select pCode language: HTML, XML (xml)

The product variable is actually a collection of XElements, even though it can never contain more than a single element (product IDs are unique). We access the ProductName column of the selected row with the expression product(0).@productName. You can call the First method to make sure you’ve selected a single product, no matter what:

Dim product = _
		(From p In products.Descendants("Product") _
		Where Convert.ToInt32(p.@ProductID) = _
			Convert.ToInt32(dtl.<ProductID>.Value) _
		Select p).FirstCode language: HTML, XML (xml)