January 2011

Something new for 2011: XML & XSD, part 2

I’m continuing my study of XML and XSD’s for January, and I realize that I ended my last post a bit abruptly.  I explained that I can cast an XML datatype to a SQL Server datatype, without giving a lot of background on WHY that’s important.  

Understanding Types.

Without going into too much detail about type, the basic reason for specifying a type for data transformations is validity; if you are expecting integer data, and the XML provides a string, then the basic contract is broken.  An XSD defines a type of data expected, and if some other type is provided, the XML is invalid.

For example, run the following code:

IF NOT EXISTS ( SELECT  *
               
FROM    sys.xml_schema_collections xsc
               
WHERE   name = 'MismatchDataType' )
       
CREATE XML SCHEMA COLLECTION MismatchDataType  AS
       
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <xsd:element name="IntValue" type="xsd:integer"/>
        </xsd:schema>'
       
GO

DECLARE @x XML(MismatchDataType)
SET @x = '<IntValue>100</IntValue>'
--SET @x = '<IntValue>String</IntValue>'

DROP XML SCHEMA COLLECTION MismatchDataType
GO

It runs fine, but if you uncomment the second SET statement (where a string value is specified), you get the following error:

Msg 6926, Level 16, State 1, Line 4
XML Validation: Invalid simple type value: ‘String’. Location: /*:IntValue[1]

What’s important to remember is that once you specify a type for an element, you may only cast that XML type to a matching SQL Server type (i.e., integer to integer, string to (n)varchar, etc.) when using the XQuery methods in SQL Server (.value(), etc.).  This is easily debuggable to a seasoned database professional; if the XML type is string, and you store a value as 100, you can easily convert that to either an integer or varchar value:

SELECT @x.value('IntValue[1]', 'integer'), @x.value('IntValue[1]', 'varchar(3)')

 

If you don’t specify a type, SQL Server can make certain assumptions regarding type conversion; however, typing your XML is one of those basic “good habits” that is foundational to application design.  Knowing what to expect from your data, regardless of whether or not it’s stored in XML or a database makes troubleshooting a lot easier in the future.

Complex vs. Simple Types

The examples I’ve used so far all rely on what is known as a simple type in XML; a simple type contains no sub-elements or attributes.  A complex element can  contain either sub-elements or attributes.  An XSD collection is especially useful when defining complex elements; the XSD allows database professionals to enforce validity in the shape of their XML, including which elements are required or not.

Most of the examples I’ve used so far have been simple elements, but a complex element enforced via an XSD would look something like  (apologies for the formatting):

CREATE XML SCHEMA COLLECTION XMLSample  AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Parent">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Child" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
        </xsd:schema>'
       
GO

 

In essence, a complex type is the heart of a strongly-typed XML document;  one of the major benefits of XML is the ability to encapsulate hierarchical data, and a complex type enforces the relationship between the elements (and attributes) encapsulated in that hierarchy much like foreign keys do for a relational database.   The presence or absence of elements in the data when compared to the XSD validate the nature of the dataset.

A stopping point…

Unfortunately for you, I need to stop at this point.  I promised myself to learn something new every month, and I feel like I have.  However, there’s so much more to learn about this topic, and I’ve simply run out of time.  I debated about spending a few more weeks on this, but then realized that I need to move on (I can always return to it in a few months) in order to stay energized about learning something new.  When I do return to this topic, I’ll be sure to post a few summary links to keep everything related.

#TSQL2sDay: Resolutions

tsql2sday For this month’s T-SQL Tuesday, Jen McCown asks:

So tell us: what techie resolutions have you been pondering, and why?  Are you heading for a certification? An award? Are you looking to pick up CLR because that guy at the Summit said it’s “bitchin’”? Go crazy…

I’ve already covered a lot of my techie resolutions in this post, but here’s a recap, with some expanded thoughts:

  • I vow to learn something new every month.  I’ve already started on this one, but I need to keep working on it.   For example, I’m working on XML and XQuery this month; next month, I’m thinking SSIS.
  • I vow to be more involved in the technical community.  I’ve slipped out of tweeting (mostly because it’s blocked on our corporate network); I will do more.  I also want to read more blogs, as well as do a LOT more blogging myself.  For example, I plan to participate in every T-SQL Tuesday for 2011.  I also plan to present at least 6 times this year.
  • I will earn my MCITP: Database Developer certification this year.  Been meaning to do it; just haven’t invested the time to do so.

On a personal note, I want to tackle a few more technical projects that have been hovering over my head:

  • I want to do more with pictures and videos.  I have a nice digital camera, and a nice Flip video camera, but I don’t do squat with them.  I’m horrible about leaving them behind when I travel; I will use them as needed.
  • My fiancée is an iPod user (like 90% of the world); I am not (I have an Archos).  Merging our music into iTunes is not going to be fun (especially since I’ve never used it), but in the long run, it’ll be the right thing to do for us.
  • I want to work smarter, not harder, so I can play more.  There’s lots of little services out there (like Remember the Milk, Yodlee.com, Google calendars, etc) which will help me manage my life on the move (shuttling between my apartment, my fiancée’s house, and my office).

Short, sweet, but at least it’s submitted 🙂

Something new for 2011: XML and XSD

As part of my New Year’s resolution for 2011, I vowed to do a deep-dive on something technical every month; for January, I’m focusing on XML.  I’ve been using XML and XQuery in SQL Server for a while now (even presenting on it), but I still don’t consider myself an expert in the area.  For example, I use a lot of untyped XML to transfer data between databases; I’ve never really tackled XSD (XML Schema Definition Language), and now’s the time.  I’m reading The Art of XSD by Jacob Sebastian to help get me started.

What’s XSD?  In a nutshell, it’s an XML document which validates the structure of another XML document.  From the perspective of a database developer, an XSD document describes how data should look in a dataset; if the data doesn’t match the description (i.e, if a table is missing a column), that dataset is invalid.  The XSD document can be very precise, or it can offer options for the dataset, but in either case, the point of an XSD is to document the expectations about the dataset.  XML without XSD is untyped; XML with an XSD is typed (although XSD’s do more than just provide information about the data types contained within the XML).

Let’s take a look at an untyped XML statement:

DECLARE @NoXSD XML
SET
@NoXSD = '<Test1>Hello World!</Test1>'
SELECT @NoXSD

 

Simple and straightforward; I created an XML variable, and populated it with an XML fragment.  I then pulled the data out of that fragment.  In this example, we have an element named Test1; what happens if we have a typo when we populate the variable?

SET @NoXSD = '<Test2>Hello World!</Test2>'
SELECT @NoXSD

 

Nothing happens.  It’s a well-formed XML fragment (no root tag, but it does have starting and ending tags); the XML engine in SQL Server doesn’t know that our fragment is supposed to have an element named Test1, so it accepts the fragment as valid.  This is where an XSD comes in:

IF EXISTS( SELECT * FROM sys.xml_schema_collections WHERE name = 'TestSchema' )
DROP XML SCHEMA  COLLECTION TestSchema
GO

CREATE XML SCHEMA COLLECTION TestSchema AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Test1" />
</xsd:schema>'
GO

DECLARE @XSD XML ( TestSchema --use the schema to validate (type) the xml
SET @XSD = '<Test1>Hello, World!</Test1>'

SELECT @XSD

 

Since the XML fragment matches the XSD,  the assignment of data works; what happens when we assign a fragment that doesn’t match?

SET @XSD = '<Test2>Hello, World!</Test2>'

We get a big fat error message:

XML Validation: Declaration not found for element ‘Test2’. Location: /*:Test2[1]

Straightforward, right?  But now what?  Well, let’s type the data in our schema:

IF EXISTS( SELECT * FROM sys.xml_schema_collections WHERE name = 'TestSchema' )
DROP XML SCHEMA  COLLECTION TestSchema
GO

CREATE XML SCHEMA COLLECTION TestSchema AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Test1" type="xsd:string"/>
</xsd:schema>'
GO

DECLARE @XSD XML ( TestSchema )
SET @XSD = '<Test1>Hello, World!</Test1>'

 

So; what does this mean?  It means that we can now use the XQuery methods built into SQL Server to cast the data from the XML datatype to a SQL Server data type.

SELECT @XSD .value ( '(//Test1)[1]' , 'varchar(50)' )

 

More to come, but that’s a good stopping place for now; we’ve built a simple XSD, and validated a simple datatype.  I plan to spend some time learning about optional data elements next.

I need ideas: building a cheap-but-powerful dev machine…

As part of my New Year’s resolutions, I’ve vowed to tackle a new technical challenge per month; however, to do so, I’d really like to beef up my home development environment, and was hoping to get some suggestions from some people on what to do.  A little background information would probably help, so let me sum up what resources I have (and some other factors influencing my decision):

Resources:

  • My company provides me with a mid-tier (LOW mid-tier) laptop for development.  While I can load some software on it, I’d rather not load my own personal development environment onto it.  I’ve thought about virtual machines, but it’s really incapable of doing much more than it already does (SQL Server Management Studio 2008 and Visual Studio 2010 can barely be opened at the same time).  It’s also a brick; traveling with it is NOT fun, but I have to carry it with me in case I need to securely access our network.
  • I have a desktop PC that was state-of-the-art 4 years ago, running Windows XP.  I’ve loaded Win 7 onto it, and the motherboard doesn’t like it.  I mostly use this PC for some minor development work, balancing my checkbook (Quicken), and browsing the Internet or watching my SageTV recordings.
  • I have a MVP MSDN subscription that I won at SQLSaturday 51 in Nashville; I know a few people were surprised that I won it (because they assumed I already had it), but I’m not an MVP (at least not an officially recognized one; see Paul Randall’s shout-out to @sqlagentman on this post.).  While I can get bits and pieces of this software to work on my home machine, I really think I’d be better off with a new environment.

Factors Influencing My Decision:

  • Budget is top priority; I have limited funds for this project.  I’d like to spend less than $500 upgrading my environment.
  • Mobility.  I do plan on traveling some to make presentations; the question is, how much of my development platform needs to be mobile?   Can I just get a superfast desktop, and do minor road development on my laptop?  On the other hand, I am spending a lot more time on the road between my apartment and the office (and my fiance’s house) these days; should I invest in a laptop, even if means I have to carry two laptops from place to place?

So, there you have it; resources and factors.  I’ve thought through a couple of scenarios, but I’m very open to suggestions:

  1. I could make my home PC a kick-a$$ development platform for under my budget.  Not very mobile, but cheap.  A couple of parts from TigerDirect, and away I go.  I might even have enough scratch left over to invest in an Android tablet or iPad for presenting (yeah, presenting; that’s how I plan to explain it).
  2. I could buy a cheap but beefy laptop and replace my home PC; not as powerful, but probably capable of doing dev work.  It’s mobile when I need it to be, but it does mean I would probably have to carry two laptops when I travel.  I’m worried that it won’t be powerful enough, but the i3 machines are pretty tasty these days.

Please feel free to leave comments with ideas; I’d love to hear some suggestions.  I’d also love to hear suggestions on 64 bit vs 32 bit (I’m assuming 64 bit); how would YOU build a cheap development platform?

the ubiquitous resolution post…

Obviously, with the start of the New Year, there will be a flood of posts on the blogosphere regarding resolutions to change bad behaviors and adopt new good ones; why should I be any different?  There’s lots of things I want to change about myself, and I figure I should put them out there and see how I’m doing over the year.  So, with little fanfare, here’s my list of challenges I plan to tackle for 2011 (broken up in to categories and subcategories for easy reference):

 

Professional

Technical Skills

  • I want to learn something new every month.  My goal is to tackle something challenging, and be able to understand the ins and outs of it within 30 days.  For example, I want to finish tackling XML (including XSD’s) in SQL Server.
  • I want to upgrade my certifications by the end of the year; I’ve been dancing around the MCITP exams for a while, and I need to finish them.

Presentation

  • I want to make at least 6 technical presentations by the end of the year; last year, I managed to eke out 8, but given some of the recent changes in my personal life (see below), I think 6 is reasonable.
  • I will blog at least once a month about some technical topic (see the first bullet point under technical skills).

Management

  • I will understand the SCRUM methodology, and learn how to implement it with my team at work.  Although I’m not a team leader, I AM the Senior Database Architect, and I need to code less, and teach more.  This is my year to do so.

 

Personal

Health

  • I’m getting married again this year, and I want to look good for my new wife.  I also want to avoid long-term health issues.  I was losing weight last year (until I started dating), and I want to get back on track.  I’d like to lose 50 lbs by October.
  • I have apnea, and I’ve been horrible about using my CPAP on a regular basis.  I will use it regularly.
  • I need to exercise more, so I will find 20 minutes a day to do SOMETHING, even if it’s just walking around the office for 20 minutes.
  • I will drink at least 8 glasses of water per day.

Spiritual

  • I’ve slacked off in my religious activities; my faith was nourished by church attendance during my divorce, and I need to start growing again.  I will find a new church in the next two months (my old church is too far to drive on a regular basis), and become a regular attendee.
  • I choose to absorb the goodness from people who love me, and I will reject the poison from those who do not.  I will focus on the important things in life (like my kids, and my future bride), and worry less about the unimportant things (like who’s mowing the grass).

Social

  • I will listen more to my children, my family, and my friends.  I will find ways to let them know I love them.
  • I will nurture my own friendships; while I love my fiance’s friends and family, I want to bring more to the table than just me.

Financial

  • My divorce pulled me way off course.  While I’m a long way from being out of debt, I will continue to make strides in that area.  I will pay off at least one credit card ahead of schedule.
  • I will save more; I plan to find ways to cut costs (like taking advantage of coupons, and eating out less).

Anyway, there you have it: my New Year’s resolutions for 2011.  May it be a good year for all.