XML Jumpstart Workbench

In which Robyn and Phil decide that the best way of starting to learn XML is to jump in and take a ride around the block.

Some of the frustration of learning XML is in not being able to see clearly an entire process. So many of the existing manuals concentrate on just one step in a chain and leave the reader saying ‘very technological. So what?’

In other cases, XML is used in illustrations with tiny fragments of real data, losing sight of the great value of XML as a means of exchanging large amounts of information.

We’ll try to take a different approach here in order to give it all some purpose. We’re not going to even try to provide anything comprehensive here, more of a quick spin around the block.

OK. Let’s assume that we want an application which seeks to list out the nearest hundred pubs to any postcode in Britain. (apologies to our many friends outside Britain).

We have two XML files that gives the raw data, collected from several publicly available sources. they are supplied with the article. One gives the location of every ‘outcode’, and the other every pub, with their postcode. Tantalising?

In this Workbench we will:

Let’s read the XML location information from a file into a table!

Reading an XML file into a SQL Server XML Variable

Assume we have the XML file unzipped in C:\workbench\locations.xml. The files can be downloaded at the bottom of the article. (‘UK Locations XML Xipped’ and ‘UK Pubs XML Zipped’) The data is over a Mb in size, so be warned! You’ll need to unzip them too!

First pull the file into a conventional relational table…

Shredding a document fragment in an XML variable into a SQL Server table

Now we’ll put it into a conventional table for working on. First we define it.

And now we can simply shred the XML data type that we’ve read in into the table, using the XML Data type nodes() method.

Querying the contents of an XML variable for a set of values as an XML result

We can extract data directly from the XML column if we wish:

Now, this is an open-ended workbench. You have a nice large source of data, and BOL is now screaming at you to be read. Try out some FLWOR!

When you tire, it is time to pull in a list of British pubs.

Assigning the result of a SQL query to an XML variable

Well, nice as far as it goes, but why not pass the result back as an XML variable, and we’ll then we can save it direct to disk, or send it happily to an application, store it in a table as a variable? XML could be quite handy!

We store the contents of an XML variable to a table

We Save an XML value to a file.

So there we have it. Hopefully, if you enjoyed this approach to XML by example, we’ll try out more complex examples in further Workbenches.

In the meantime, there are other resources on the Simple-Talk site:

Beginning SQL Server 2005 XML Programming, Srinivas Sampath 21 February 2006
XML has been used to represent semi-structured (as well as unstructured) data such as documents and emails. If information in these models has to be queried, then XML is probably the simplest way to represent such information. Read more…

Understanding XML web services for testers, Helen Joyce – 13 April 2003
This White Paper investigates how XML web services are implemented, considers the customizable features of web services and looks at load testing a web service, specifically so that test strategies can be formulated. Read more…

XML and RDBMS: 10 years on, Jim Fuller – 25 August 2006
As we approach the 10-year anniversary of XML Jim Fuller provides a personal retrospective, focussing on how XML has been and will be used with the RDBMS. Read more…

SQL Server XML Cribsheet, Robyn Page – 29 March 2007
If you find XML a bit of an acronym minefield, Robyn’s Cribsheet will help sort out your XSLT from your XDM. Read more…

And we would also recommend you to read Jacob Sebastian’s excellent articles on SQL Server Central: