Typed XML in SQL Server 2005

Moving on

I'm over 2 months late in writing this post. please forgive me for the delay, especially those of you who were nice enough to leave comments and questions and expected a response. 

 

This is my last posting on my "Typed XML" blog. After almost 9 years at Microsoft I felt the need to do something else and on August 13th I joined the ranks of a well known online retailer. You'll still find some of my contributions in SQL Server 2008 when it is released. The entire team did a great job addressing some of the issues most often mentioned by customers. I don't want to steal anybody's thunder (or attract the attention of any lawyers :-) ) so I won't say much more but keep an eye out for that next version. I's going to rock!

Those 8 and 3/4 years at Microsoft were quite a ride. Thanks to everybody who helped make that time special. 

Silent XQuery failures

A newsgroup post I read earlier today reminded me that there are cases where failure to get the expected result when using XQuery can sometimes be difficult to diagnose.

Here’s what the poster was doing

declare @xml xml

set @xml = '<root><test></test></root>'

set @xml.modify('replace value of (/root/test/text())[1] with "test

new value"')

select @xml

This user expected to get something like <root><test>test new value</test></root> but instead, the resulting instance looked like <root><test /></root>.

The server never gave any error or warning, yet the XML instance appeared to be untouched.

The problem, as you might have guessed already, is that XPath expression (/root/test/text())[1] returns the empty sequence. In the original instance, ‘test’ is an empty element. It has no children at all. Therefore the expression (/root/test/text())[1] doesn’t point to any existing node. The solution to this problem is to insert a new test node inside the ‘test’ element, like this

set @xml.modify('insert text{"test new value"} as first into (/root/test)[1] ')

 

In general, when you use the modify() method and the target XPath expression returns the empty sequence, nothing happens. The only exception to this rule is when the server can statically determine that the result will be empty. That’s when XML schemas come in handy.

For example, let’s look at the following example

 

DECLARE @x XML

SET @x = '<root><x/><y/><z/></root>'

SET @x.modify('delete /root[1]/a[1]')

 

Let’s suppose I made a typo and instead of typing ‘z’ in my XPath expression I typed ‘a’. The query doesn’t do anything because with this particular XML instance the XPath expression /root[1]/a[1] returns the empty sequence (it doesn’t point to any existing node). There is no way to know that before executing the query. Statically, the type of this XPath expression is element(a, xdt:untyped)?

 

Now, let’s try something similar when a schema collection is associated with the XML variable.

 

CREATE XML SCHEMA COLLECTION SC AS '

<schema xmlns="http://www.w3.org/2001/XMLSchema">

 

      <element name="root">

            <complexType>

                  <sequence>

                        <element name="x" type="string" minOccurs="0"/>

                        <element name="y" type="string" minOccurs="0"/>

                        <element name="z" type="string" minOccurs="0"/>

                  </sequence>

            </complexType>

      </element>

 

</schema>'

go

 

DECLARE @x XML(SC)

SET @x = '<root><x/><y/><z/></root>'

SET @x.modify(' delete /root[1]/a[1]')

 

This time the query fails with the following error message:

XQuery [modify()]: There is no element named 'a' in the type 'element(root,#anonymous) ?'.

 

The server was able to use the schema to determine that my XPath expression will never point to an existing node and sent me a helpful error message. Even though typing your data doesn’t mean you’ll catch all those mistakes, it still helps when debugging XML queries.

 

-
Disclaimer:
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.

 

Stardust memories

The famous Stardust hotel and casino was torn down today. I was fond of the place since this was the hotel I stayed at when I first visited Vegas in the mid nineties. During our most recent trip to Nevada last summer my wife and I made sure we stopped by to bid farewell to the place. Its history was deeply linked to that of the town. Illustrious artists performed there. If you’ve seen the movie Casino, the story was in large part inspired by events involving the ownership of the Stardust. Yet, despite its past, the Stardust still had to go to make room for a new 4+ billion resort. By comparison, when the famous Parisian concert hall L’Olympia was threatened by developers, there was a public outcry to save it. Oh well … nothing is forever and I’ll always have those wonderful memories.

Mixed content (part 3)

It’s been a few weeks since my last post about mixed content so let’s pick up right where we left off.

This time we will look at DML operations on mixed content.  We’ll keep using the XML schema as well as the table I created in the first post of this series.

 

Let’s insert an instance to work with.

INSERT INTO T VALUES (3, CONVERT(XML(SC_Letter), '<l:letter xmlns:l="urn:letter" xmlns:t="urn:letter:tags">

      <l:date>2006-10-15-08:00</l:date>

      <l:sender>

            <l:FirstName>James</l:FirstName>

            <l:LastName>Kirk</l:LastName>

            <l:Line1>1234 Elm Street</l:Line1>

            <l:City>Redmond</l:City>

            <l:ZIPCode>98052</l:ZIPCode>

            <l:State>WA</l:State>

      </l:sender>

      <l:recipient>

            <l:FirstName>Jean-Luc</l:FirstName>

            <l:LastName>Picard</l:LastName>

            <l:Line1>13 rue des Lilas</l:Line1>

            <l:City>Ronchin</l:City>

            <l:ZIPCode>59555</l:ZIPCode>

            <l:Country>France</l:Country>

      </l:recipient>   

Dear Sir,

 

 

      I received your letter dated <t:date>2006-10-10+00:00</t:date> this morning. It would be my pleasure to attend the annual symposium <t:title>XML database systems in class-B Federation Spacecrafts</t:title>.

 

Please contact my assistant <t:person>Mr. Spock</t:person> at <t:email>spock@space-federation.org</t:email> to make all necessary arrangements.

 

 

Sincerely,

 

 

James T. Kirk

</l:letter>', 1))

go

 

 

DML operations on elements

There are no behavior differences between element-only content and mixed content. You can insert, delete, and replace the value of elements provided the resulting instance validates against the schema.

For example, let’s update the name of the assistant mentioned in the letter.

 

UPDATE T SET xmlCol.modify(' declare namespace l = "urn:letter";

declare namespace t = "urn:letter:tags";

replace value of (/l:letter//t:person[data(.) = "Mr. Spock"])[1] with "Capt. Spock"')

WHERE iCol = 3

go

 

You can query the instance back and see for yourself that the value of the element has been updated.

We could also decide not to mention the assistant by name and delete the element.

 

UPDATE T SET xmlCol.modify(' declare namespace l = "urn:letter";

declare namespace t = "urn:letter:tags";

delete (/l:letter//t:person[data(.) = "Capt. Spock"])[1]')

WHERE iCol = 3

go

If you query the instance back you can see that the letter now says

Please contact my assistant  at <t:email>spock@space-federation.org</t:email> to make all necessary arrangements.

 

Something very interesting happened here that is not immediately visible. If you remember, in our previous posts, we used the following query to examine the nodes in our instance

SELECT  x.query('.'), x.query('if (. instance of text()) then "text" else

      if (. instance of element()) then "element" else ""')

FROM T

CROSS APPLY xmlCol.nodes('/*:letter[1]/node()') AS Tref(x)

WHERE iCol = 3

Had we run this query prior to deleting the <person> element, we would’ve seen this as part of our results (partial results printed to save space)

 

. Please contact my assistant	text
<p1:person xmlns:p1="urn:letter:tags">Capt. Spock</p1:person>	element
at	text
<p1:email xmlns:p1="urn:letter:tags">spock@space-federation.org</p1:email>	element
to make all necessary arrangements. Sincerely, James T. Kirk	text

 

Now that we’ve deleted the element, the results look like this (partial results again)

. Please contact my assistant  at	text
<p1:email xmlns:p1="urn:letter:tags">spock@space-federation.org</p1:email>	element
to make all necessary arrangements. Sincerely, James T. Kirk	text

 

Since we delete an element that was between two text nodes, those nodes merged to form only one.

 

DML operations on text nodes

 

Text nodes can be updated, inserted or deleted.

First, let’s replace the value of a text node.

 

UPDATE T SET xmlCol.modify(' declare namespace l = "urn:letter";

declare namespace t = "urn:letter:tags";

replace value of /l:letter[1]/text()[6] with ". I suggest that you talk to my asisstant, who can be contacted at "')

WHERE iCol = 3

go

You can query the instance to see that the text of the letter was modified appropriately.

 

Now let’s insert a text node after an existing one

 

 

UPDATE T SET xmlCol.modify(' declare namespace l = "urn:letter";

declare namespace t = "urn:letter:tags";

insert text{"&#xD;&#x9;&#xD;&#x9;PS: Live long and prosper."} as last into /l:letter[1]')

WHERE iCol = 3

go

 

If you look at the nodes, you’ll see that the new text node we inserted merged with the one preceding it to form one text node so that all text between two elements tags counts as only one text node (partial results printed below)

. I suggest that you talk to my asisstant, who can be contacted at	text
<p1:email xmlns:p1="urn:letter:tags">spock@space-federation.org</p1:email>	element
to make all necessary arrangements.     Sincerely,     James T. Kirk                                   PS: Live long and prosper.	text

 

 

-
Disclaimer:
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm. 

A new SQL CLR blog

One of my colleagues, Brad Richards just started blogging about SQL CLR performance. If you use SQL CLR I strongly suggest that you take a look at this blog. Brad is very knowledgeable about performance and you're likely to learn some very valuable information.

Mixed content (part 2)

Following last week's introduction to the concept of mixed content, let's look in detail at a validated XML instance with mixed content.

 

Taking a look at the children nodes of the <letter> element

 

Let’s reuse the instance we stored in our table last time. We can query it and look at all the nodes under the “letter” root element. With the nodes() method we can output a table. This table will contain a row for each node under <letter>. The first column contains the node itself, the second column contains the value “text” if it is a text node or “element” if it is an element node.

 

SELECT  x.query('.'), x.query('if (. instance of text()) then "text" else

      if (. instance of element()) then "element" else ""')

FROM T

CROSS APPLY xmlCol.nodes('/*:letter[1]/node()') AS Tref(x)

WHERE iCol = 1

 

The results look something like this (I formatted the cells containing element nodes so that the whole XML fragment is visible. If you run the query yourself you’ll notice no such formatting.)

 

 

<p1:date xmlns:p1="urn:letter">2006-10-15-08:00</p1:date>	element
<p1:sender xmlns:p1="urn:letter"><p1:FirstName>James</p1:FirstName><p1:LastName>Kirk</p1:LastName><p1:Line1>1234 Elm Street</p1:Line1><p1:City>Redmond</p1:City><p1:ZIPCode>98052</p1:ZIPCode><p1:State>WA</p1:State></p1:sender>	element
<p1:recipient xmlns:p1="urn:letter"><p1:FirstName>Jean-Luc</p1:FirstName><p1:LastName>Picard</p1:LastName><p1:Line1>13 rue des Lilas</p1:Line1><p1:City>Ronchin</p1:City><p1:ZIPCode>59555</p1:ZIPCode><p1:Country>France</p1:Country></p1:recipient>	element
Dear Sir,  I received your letter dated	text
<p1:date xmlns:p1="urn:letter:tags">2006-10-10Z</p1:date>	element
this morning. It would be my pleasure to attend the annual symposium	text
<p1:title xmlns:p1="urn:letter:tags">XML database systems in class-B Federation Spacecrafts</p1:title>	element
. Please contact my assistant	text
<p1:person xmlns:p1="urn:letter:tags">Mr. Spock</p1:person>	element
at	text
<p1:email xmlns:p1="urn:letter:tags">spock@space-federation.org</p1:email>	element
to make all necessary arrangements. Sincerely, James T. Kirk	text

 

 

Let’s now reprint the entire instance, as we submitted it to the server, with the text nodes colored in green.

 

<l:letter xmlns:l="urn:letter" xmlns:t="urn:letter:tags">

      <l:date>2006-10-15-08:00</l:date>

      <l:sender>

            <l:FirstName>James</l:FirstName>

            <l:LastName>Kirk</l:LastName>

            <l:Line1>1234 Elm Street</l:Line1>

            <l:City>Redmond</l:City>

            <l:ZIPCode>98052</l:ZIPCode>

            <l:State>WA</l:State>

      </l:sender>

      <l:recipient>

            <l:FirstName>Jean-Luc</l:FirstName>

            <l:LastName>Picard</l:LastName>

            <l:Line1>13 rue des Lilas</l:Line1>

            <l:City>Ronchin</l:City>

            <l:ZIPCode>59555</l:ZIPCode>

            <l:Country>France</l:Country>

      </l:recipient>   

Dear Sir,

 

      I received your letter dated <t:date>2006-10-10+00:00</t:date> this morning. It would be my pleasure to attend the annual symposium <t:title>XML database systems in class-B Federation Spacecrafts</t:title>.

 

Please contact my assistant <t:person>Mr. Spock</t:person> at <t:email>spock@space-federation.org</t:email> to make all necessary arrangements.

 

Sincerely,

 

James T. Kirk

</l:letter>

 

 

You’ll notice that all text between two elements tags counts as one text node. Our instance contains five of them. You can verify the accuracy of that count with the following query, which returns 5 also

 

SELECT xmlCol.value('count(/*:letter/text())','int') FROM T WHERE iCol = 1

go

 

 

Whitespace-only text nodes

 

You might also have noticed that our instance contained portions of text made only of whitespace characters (between the “date” and the “sender” element for example). Those weren’t preserved as text nodes. Whitespace-only text nodes are actually a very interesting topic that isn’t limited to mixed content and we’ll probably talk about it some more in the future but for now, let’s look at what one should do when they wish to preserve them. There is an optional parameter in the T-SQL convert() function, that when set to 1, forces the server to preserve whitespace-only text nodes when converting from a  string type to XML. In our case, we should do this.

 

INSERT INTO T VALUES (2, CONVERT(XML(SC_Letter), '<l:letter xmlns:l="urn:letter" xmlns:t="urn:letter:tags">

      <l:date>2006-10-15-08:00</l:date>

      <l:sender>

            <l:FirstName>James</l:FirstName>

            <l:LastName>Kirk</l:LastName>

            <l:Line1>1234 Elm Street</l:Line1>

            <l:City>Redmond</l:City>

            <l:ZIPCode>98052</l:ZIPCode>

            <l:State>WA</l:State>

      </l:sender>

      <l:recipient>

            <l:FirstName>Jean-Luc</l:FirstName>

            <l:LastName>Picard</l:LastName>

            <l:Line1>13 rue des Lilas</l:Line1>

            <l:City>Ronchin</l:City>

            <l:ZIPCode>59555</l:ZIPCode>

            <l:Country>France</l:Country>

      </l:recipient>   

Dear Sir,

 

      I received your letter dated <t:date>2006-10-10+00:00</t:date> this morning. It would be my pleasure to attend the annual symposium <t:title>XML database systems in class-B Federation Spacecrafts</t:title>.

 

Please contact my assistant <t:person>Mr. Spock</t:person> at <t:email>spock@space-federation.org</t:email> to make all necessary arrangements.

 

Sincerely,

 

James T. Kirk

</l:letter>', 1))

go

 

 

Let’s rerun the same query as last time for this new instance

 

 

SELECT  x.query('.'), x.query('if (. instance of text()) then "text" else

      if (. instance of element()) then "element" else ""')

FROM T

CROSS APPLY xmlCol.nodes('/*:letter[1]/node()') AS Tref(x)

WHERE iCol = 2

 

 

This time the results look like this (once again I altered the way results are displayed for your reading