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.


 

Comments (4)
  1. Sorry for posting it here:

    SELECT * FROM

    (

    select top 3 ad_id, ad_name from ad

    UNION

    select top 3 ad_CAMPAIGN_id, ad_CAMPAIGN_name from ad_CAMPAIGN

    ) AS TABLE1

    order by ad_name asc

    This gives me the right results, sorted by ad_name. but when I use FOR XML EXPLICIT here. The results comes sorted by ad_id (which is a primary key)

    Why this happens.

  2. my email address is saurabhdotnet@hotmail.com

    Help required??

  3. Mahendran says:

    the post was really very useful.. its solved my xml delete problem thanks a lot

  4. Andy Abel says:

    Except that in my case it doesn’t help. Since I want to use the @xml.modify() to distinguish between an empty element:-

    ‘<root><test></test></root>’

    and no element:-

    ‘<root></root>’

    Any suggestions on how I can tell the difference? Thanks.

Comments are closed.

Skip to main content