Archive for the ‘.NET’ Category

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:

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

IFOBJECT_ID('studentdb.marvel_xml','U')ISNOTNULLDROPTABLE marvel_xml;

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:

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:

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.

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;

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:

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 versionThe Hobbit: An Unexpected Journey Bluray and DVD on March 19th and then an extended version later in the year. Ouch!!! He’s getting into my wallet again.

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.