MacLochlainns Weblog

Working through Chapter 7 of the Querying Microsoft SQL Server 2012 book for Microsoft’s Exam 70-461, I found the XML examples incomplete for my students. I decided to put together a post on how to create:

• An XML Schema Collection type.
• A table that uses an XML Schema Collection as a column’s data type.
• An example on how you can transfer the contents of a table into the XML Schema Collection.

This post assumes you understand the basics about XML structures. If you’re unfamiliar with XML, please note that everything within it is case sensitive unlike SQL. You raise exceptions when the case of your XML fails to match the case of your XML Schema Collection definitions. I raised the following exception by using a Marvel element tag in title case when the XML Schema Collection uses a lowercase marvel element tag:

Msg 6913, Level 16, State 1, Line 2
XML Validation: Declaration not found for element 'Marvel'. Location: /*:Marvel[1]

The basic marvel table is defined in this earlier blog post. To ensure you don’t run into conflicts with previously existing objects, you can delete the marvel table with the following syntax:

1
2

IF OBJECT_ID('studentdb.marvel_xml','U') IS NOT NULL
  DROP TABLE marvel_xml;

There is no predefined function that lets you conditionally drop the XML Schema Collection from the data base. The alternative is to query the Microsoft SQL Server data catalog for the existence of a row before dropping the XML Schema Collection, like this:

1
2
3
4

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'MarvelXMLTable')
  DROP XML SCHEMA COLLECTION MarvelXMLTable;
ELSE
  SELECT 'Not found.';

You can now create the MarvelXMLTable XML Schema Collection with the following syntax:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE XML SCHEMA COLLECTION MarvelXMLTable AS
N'<?xml version="1.0" encoding="UTF-16"?>
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
                schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="Marvel">
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="marvel_id" type="sqltypes:int" />
          <xsd:element name="avenger_name" type="sqltypes:nvarchar" />
          <xsd:element name="first_name" type="sqltypes:nvarchar" />
          <xsd:element name="last_name" type="sqltypes:nvarchar" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>';

After creating the XML Schema Collection, you can confirm whether it works correctly with the following statements. It will fail when you use semicolons on the DECLARE or SET lines, so avoid them as shown below:

1
2
3
4
5
6
7
8

DECLARE @marvel AS XML(MarvelXMLTable)
SET @marvel = '<marvel>
                 <marvel_id>1</marvel_id>
                 <avenger_name>Falcon</avenger_name>
                 <first_name>Sam</first_name>
                 <last_name>Wilson</last_name>
               </marvel>'
SELECT @marvel;

After creating and verifying the integrity of the XML Schema Collection, you can create a marvel_xml table. The xml_table column of the marvel_xml table uses a strongly-typed XML type, as shown:

1
2
3

CREATE TABLE marvel_xml
( id        INT IDENTITY(1,1) CONSTRAINT marvel_xml_pk PRIMARY KEY
, xml_table XML(MarvelXMLTable));

You can write an INSERT statement with single element like this:

1
2
3
4
5
6
7
8
9

INSERT INTO marvel_xml 
( xml_table )
VALUES
('<Marvel>
    <marvel_id>9</marvel_id>
    <avenger_name>Falcon</avenger_name>
    <first_name>Sam</first_name>
    <last_name>Wilson</last_name>
  </Marvel>');

You can insert two elements with an INSERT statement like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

INSERT INTO marvel_xml
( xml_table )
VALUES
('<Marvel>
    <marvel_id>9</marvel_id>
    <avenger_name>Falcon</avenger_name>
    <first_name>Sam</first_name>
    <last_name>Wilson</last_name>
  </Marvel>
  <Marvel>
    <marvel_id>10</marvel_id>
    <avenger_name>Scarlet Witch</avenger_name>
    <first_name>Wanda</first_name>
    <last_name>Witch</last_name>
  </Marvel>');

There’s a lot of typing to insert XML literal values. It a lot easier to transfer relational data from a table to an XML type. Unfortunately, Microsoft didn’t make it very easy. The FOR XML AUTO doesn’t work for two reasons. First, the FOR XML AUTO clause doesn’t render the correct XML structure. Second, the INSERT statement disallows nested SELECT queries that include the FOR XML AUTO clause.

Here’s the way you insert relational data into an XML type column:

1
2
3
4
5
6
7
8
9
10
11
12
13

DECLARE @xml_elements NVARCHAR(MAX);
SET @xml_elements = N'';
WITH x AS
(SELECT CONCAT(N'<marvel>'
              ,N'<marvel_id>',marvel_id,N'</marvel_id>'
              ,N'<avenger_name>',avenger_name,N'</avenger_name>'
              ,N'<first_name>',first_name,N'</first_name>'
              ,N'<last_name>',last_name,N'</last_name>'
              ,N'</marvel>') AS element
 FROM   marvel)
SELECT @xml_elements += element FROM x;
INSERT INTO marvel_xml (xml_table)
VALUES (@xml_elements);

Line 1 declares an xml_element session variable. Line 2 initializes the xml_element session variable as an empty string. The WITH clause on lines 3 through 10 creates a Common Table Expression (CTE) with the valid XML structure for all rows in the marvel table. The nested SELECT statement on line 11 returns data from the CTE and adds returned row to the session-level string variable. Finally, the INSERT statement on lines 12 and 13 inserts the XML table into the xml_table column.

You can then query the table with the following statement:

SELECT xml_table
FROM   marvel_xml;

Click on the return type in the SQL Server Management Studio (SSMS), and the XML structure will expand to show this:

<marvel>
  <marvel_id>1</marvel_id>
  <avenger_name>Hulk</avenger_name>
  <first_name>Bruce</first_name>
  <last_name>Banner</last_name>
</marvel>
<marvel>
  <marvel_id>2</marvel_id>
  <avenger_name>Iron Man</avenger_name>
  <first_name>Tony</first_name>
  <last_name>Stark</last_name>
</marvel>
<marvel>
  <marvel_id>3</marvel_id>
  <avenger_name>Black Widow</avenger_name>
  <first_name>Natasha</first_name>
  <last_name>Romanoff</last_name>
</marvel>
<marvel>
  <marvel_id>4</marvel_id>
  <avenger_name>Thor</avenger_name>
  <first_name>Thor</first_name>
  <last_name>Odinsson</last_name>
</marvel>
<marvel>
  <marvel_id>5</marvel_id>
  <avenger_name>Captain America</avenger_name>
  <first_name>Steve</first_name>
  <last_name>Rogers</last_name>
</marvel>
<marvel>
  <marvel_id>6</marvel_id>
  <avenger_name>Hawkeye</avenger_name>
  <first_name>Clint</first_name>
  <last_name>Barton</last_name>
</marvel>
<marvel>
  <marvel_id>7</marvel_id>
  <avenger_name>Winter Soldier</avenger_name>
  <first_name>Bucky</first_name>
  <last_name>Barnes</last_name>
</marvel>
<marvel>
  <marvel_id>8</marvel_id>
  <avenger_name>Iron Patriot</avenger_name>
  <first_name>James</first_name>
  <last_name>Rhodey</last_name>
</marvel>

As always, I hope this post benefits those who read it.

My take on the good, the bad, and the ugly of our Winter season, beside all the snow I’ve shoveled and blown to get in and out of my driveway, is:

The Good?

February brought us MySQL 5.6 GA (General Availability) and Ruby 2.0.0-p0. Mega thanks to the MySQL and Ruby teams! By the way, if you’ve always wanted to know a little about Ruby without a great deal of effort, you should check this awesome little 20 minute Ruby tutorial. Naturally, the best Ruby Programming book is a bit dated, and maybe there will be a new one later in the year. If anybody knows whether there’s a new edition forthcoming, I’d appreciate hearing about it! Another more current, Ruby 1.9, book is Programming Ruby 1.9: The Pragmatic Programmers’ Guide.

The Bad?

It seems like a lot of folks who implemented Microsoft SharePoint are rapidly preparing to migrate from SharePoint 2010 to 2013, but what’s unclear is whether they’re upgrading for the features or bug fixes. It does seem many are opting for the Microsoft cloud services, and rumor has it that’s because a little collaboration requires a lot of hardware in the data center. It’s probably a good idea to do some research before implementing Microsoft SharePoint.

The Ugly?

If NBC got the quote right today, President Obama disappointed me by saying, “… I should somehow do a Jedi mind meld with these (republican) folks …” How can the President not know that Star Trek Vulcan’s aren’t in the Star Wars universe? Worse yet, my disappointment with Peter Jackson returns because he is releasing the theatrical version The Hobbit: An Unexpected Journey Bluray and DVD on March 19^th and then an extended version later in the year. Ouch!!! He’s getting into my wallet again.

Spring starts later this month and the rumor is that Oracle will release Oracle Database 12c soon. Then, we get Iron Man 3, Star Trek into Darkness, and the Lone Ranger, along with warm weather.

The last day of Oracle Open World 2010. My focus today was on attending OracleDevelop and JavaOne.

The weather picked up today and it was a nice warm Indian Summer day. There were lots of tourists out too. The photo is taken of the building where you board the Powell Street cable car. There was a lot of walking with the way the events are dispersed among the Moscone South, Moscone West, Marriott Hotel, Westin Hotel, Hilton Hotel, W Hotel, and Hotel Nikko. I can’t quite remember how many times I walked back and forth across the 6 blocks between the Moscone centers and Union Square hotels. I can tell you that we went only twice to Mel’s Drive-in.

It was amazing to see how quickly the various conference expedition centers shutdown, packed up, and had their materials shipped out. We had to step over all the plywood that protected tiles and carpets to attend events.

Day 4 also brought a smaller audience for venues. I’m not quite sure if they left earlier or slept in because they were out too late last night attending the Wednesday night event on Treasure Island. The reduced number of attendees was great for those of us who remained. You can see how few attended the .NET/Oracle hands-on lab in the Hilton, which made finding a nice spot easy. You can find the .NET/Oracle hands-on tutorial materials on the Oracle Technical site.

If you opt to use the tutorials, you may benefit from these hints. You should be able to avoid some of the issues that I ran into when working through the open labs. First, you should expand the Microsoft Studio to full screen. Second, you should look for context pop-ups attached to a small arrow at the top right corner of grids, et cetera. Lastly, there are a few small mistakes that you’ll need to work through. Look at the errors as an opportunity to think and experiment and they’re great basic .NET/Oracle tutorials.

Oracle Open World 2010 is done. Time to review the keynotes for those things that I missed while listening to them, and consider the new role of Tuxedo in the life of Oracle’s product stack. It’s also time to download and play with the MySQL 5.5 candidate release; and it’s time to kick off my shoes, put up my feet, and play with the technology again.

Tomorrow I turn my fate over to the airlines, and hope to arrive home on schedule.