Here is something that we as developers should be aware of in working with an XQuery statement (.query on an XML datatype) in SQL Server 2005: if a Where clause returns false, then you may end up with an empty xml data type. I believe this would return an empty string if you are using ADO (I'll try it later).
I asked Michael Rhys about this in his presentation on XQuery at PDC. He said that since you are selecting on an XML column with the query operation, you must return something - even if the XQuery statement itself returns nothing - UNLESS you check the existence in the Transact SQL Where clause.
Here is an example of the problem:
|
-- Create a table with an XML column
CREATE TABLE customerData(
ID int IDENTITY(1,1) NOT NULL,
customerDocs xml NOT NULL,
updated datetime NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_customerData] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Let's add some data
INSERT INTO customerData(customerDocs)
VALUES(N'<?xml version="1.0"?>
<customers>
<customer name="bob" status="current"/>
<customer name="sally" status="no order FY2005"/>
<customer name="judy" status="no order FY2004"/>
<customer name="mark" status="current"/>
</customers>')
GO
-- Let's view it
SELECT * FROM customerData
GO |
The results will be something like this:
| 2 |
<customers><customer name="bob" status="current" /><customer name="sally" status="no order FY2005" /><customer name="judy" status="no order FY2004" /><customer name="mark" status="current" /></customers> |
2005-09-14 09:05:20.780 |
Here is an XQuery statement that has a valid where clause:
SELECT customerDocs.query('
for $CUSTOMER in //customer
where $CUSTOMER/@status eq "current"
return $CUSTOMER') AS RESULTS
FROM customerData
This returns an XML data type
<customer name="bob" status="current" /><customer name="mark" status="current" />
But what about this XQuery statement for which the where clause is false:
SELECT customerDocs.query('
for $CUSTOMER in //customer
where $CUSTOMER/@status eq "NOT current"
return $CUSTOMER') AS RESULTS
FROM customerData
The return value is an empty XML data type!!! This may not be what developers are expecting.
Michael Rhys suggested that the best practice for an XQuery that have a false where clause is to put an exists condition in the outer Transact SQL where clause, like this:
SELECT customerDocs.query('
for $CUSTOMER in //customer
where $CUSTOMER/@status eq "NOT current"
return $CUSTOMER') AS RESULTS
FROM customerData
WHERE
1 = customerDocs.exist('//customer[@status eq "NOT current"]')
This will return an empty record set, rather then an a record set with an empty XML type, if there is no data within the XML document that satisfies the where clause.