LibraryTech NotesWorking with XML Data in SQL Azure

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.

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

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.

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.

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.

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.

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:

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

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.

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.

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

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.

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.

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

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

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

Find out for yourself how productive you can be by using Altova tools to work with XML data in the SQL Azure cloud!

 Click here to download a fully functional, free 30-day trial of the Altova MissionKit!