Using Altova Tools to Work with XML Data in the SQL Azure Cloud

Tech Note 23 discussed connecting to Microsoft SQL Azure databases with Altova DatabaseSpy and XMLSpy and demonstrated a database query from XMLSpy into a table in the SQL Azure cloud.

This Tech Note demonstrates a method to migrate an existing table to SQL Azure and shows you some tricks you can do with XML in the cloud. You can start by creating a new database schema in SQL Azure in your SQL Azure account. Then you can create a DatabaseSpy project with a connection to a local copy of SQL Server Express running the AdventureWorks sample database, and a second connection to your new SQL Azure schema.

DatabaseSpy Project helper window

The AdventureWorks database contains a table called JobCandidate with some XML data you can use as a model for SQL Azure XML contents.

AdventureWorks JobCandidate table in the DatabaseSpy Online Browser window

The right-click context menu lets you generate a CREATE statement for the existing table to use as a basis for the SQL Azure version.

CREATE statement for the existing JobCandidate table

You will need to modify this statement to execute in the SQL Azure database. In addition to changing the database and schema names, you can remove the foreign key constraint to the Employee table, since the new database doesn’t contain a table with that name.

Also, SQL Azure does not support the CONTENT keyword, so you can remove that as well.

Modifying the CREATE statement in a DatabaseSpy SQL Editor window

After making sure the Properties window for the revised CREATE statement points to the SQL Azure database, you can execute the statement. When you refresh the database and expand the view in the Online Browser helper window, you can see the new empty table.

New table viewed in the DatabaseSpy Online Browser window

A data comparison between the existing table and the new one will allow you to create a script to migrate data into the new table in the SQL Azure cloud. This is similar to the database content comparison described in Tech Note 23. Instead of merging data directly, simply save the merge script.

DatabaseSpy Merge Data dialog

If you try to execute the script as generated, it will fail with an error message saying that SQL Azure cannot insert values into the new table when IDENTITY_INSERT is set to OFF. You can add a line to the merge script to SET INDENTITY_INSERT ON and re-execute:

Merge data script viewed in a DatabaseSpy SQL Editor window

Next, you can run a SELECT query to view the data that was successfully uploaded.

Select query and Results Grid viewed in DatabaseSpy

The DatabaseSpy Data Inspector window lets you more easily examine the contents of a wide column, and is ideal to use for XML documents stored in the Resume column of the new JobCandidate table.

DatabaseSpy Data Inspector window

Editing XML Data with XMLSpy

If you need to revise, edit, update, or validate XML data in a SQL Azure database, Altova XMLSpy provides more robust XML editing features than DatabaseSpy. You can connect to your SQL Azure database from XMLSpy and run a SELECT query from the XMLSpy Database Query window. XMLSpy lets you open any XML row for direct editing, with access to advanced XML editing functionality.

XMLSpy Database Query view of XML data in the SQL Azure cloud

Of course all the familiar features of the XMLSpy text view and grid view are available.

XMLSpy Text view of XML data in the SQL Azure cloud

XMLSpy Grid view of XML data in the SQL Azure cloud

After your edits are complete, the XML File / Save menu option saves the revised XML document to the same row of the JobCandidate table in the SQL Azure database in the cloud.

XMLSpy File / Save menu option saves XML data to the SQL Azure cloud

Parsing XML Data with XQuery

You can also apply the XMLSpy XQuery editor, with its built-in knowledge of XQuery syntax and context-sensitive entry helpers to build XQuery statements that parse the XML data in your SQL Azure database. The XQuery statement below extracts and returns the home addresses from the XML resumes where JobCandidateID is less than 7.

XMLSpy XQuery editor for XML data in SQL Azure

The XQuery statement can be executed in the Database Query window, with results immediately available to work with in XMLSpy.

XQuery statement and results in the XMLSpy Database Query window

Of course the XQuery result can also be edited in Text view or in Grid view.

XQuery result in XMLSpy Text view

And you can save the query result either from the Database Query window or from the XML Editor view.

XQuery result in XMLSpy Grid view

Find out for yourself how productive you can be by using Altova tools to work with XML data in the SQL Azure cloud! Download a 30-day trialnow.