XML querying in SQL2000

Dr.NETjes — Wed, 02 Mar 2005 22:02:00 GMT

Do you want to query on unstructured xml data inside SQL2000 text fields? Keep on reading!

I've created a table 'Person' with a text field containing flat xml data (see below):

Id [identity]	FirstName [varchar(50)]	ContentXml [text]
1	Dion	<content><birthdate>06/21/1970</birthdate><gender>male</gender></content>
2	Rosie	<content><birthdate>11/30/1969</birthdate><gender>female</gender></content>
3	James	<content><birthdate>05/02/1973</birthdate><gender>male</gender></content>
4	Bill	<content><birthdate>06/12/1961</birthdate><gender>male</gender></content>

Now I'd like to query the xml-data in the 'ContentXml' field, for example:

“Select all rows where 'birthdate' is at least '1/1/1970', and 'gender' equals 'male'. “

Using SQLServer2005, querying on data inside xml fields is as easy as:

SELECT *
FROM   Person
WHERE  ContentXml.value('(//birthdate)[1]', 'DateTime') >= CAST('1/1/1970' AS DateTime)
       AND ContentXml.value('(//gender)[1]', 'VarChar') = 'male'

But what if you're using SQLServer2000 and want a solution right now? SQLServer2000 doesn't “know” xml.....
Well, take a look at the query below, running on SQLServer2000 and returning the same results:

SELECT *
FROM   Person
WHERE  dbo.fn_XmlElementValue(ContentXML, 'birthdate') >= CAST('1/1/1970' AS DateTime)
       AND dbo.fn_XmlElementValue(ContentXML, 'gender') = 'male'

You want to see the fn_XmlElementValue function? Here it is:

CREATE FUNCTION dbo.fn_XmlElementValue(@xml text, @tagname varchar(100)) 
RETURNS varchar(2000) 
AS 
BEGIN 
 DECLARE @startpos int, @endpos int 
 IF (NOT @tagname IS NULL) 
 BEGIN 
  SET @startpos = CHARINDEX('<' + @tagname + '>', @xml) + LEN('<' + @tagname + '>') 
  IF (@startpos > 0) 
  BEGIN 
   SET @endpos = CHARINDEX('</' + @tagname + '>', @xml, @startpos) 
   IF (@endpos > @startpos) 
   BEGIN 
    -- Return the requested value 
    RETURN (SUBSTRING(@xml, @startpos, @endpos - @startpos)) 
   END 
  END 
 END 
 --  Tag empty or not found 
 RETURN NULL 
END

Let me explain what happens here: The field 'ContentXml' is searched for the string-value between the '<birthdate>' and '</birthdate>' tags, and the result is compared with date '01/01/1970'. Cool, eh?

Is this slow? No, certainly not! I just inserted 10.000 Persons in my table, containing xml data with random birthdates between 1/1/1960 and 1/1/1980. The select query is still very quick, and seems even quicker than the corresponding SQLServer2005 query...

Note: the given solution is only suitable for flat xml data, where the elements have unique tag-names and don't have attributes but only values.
But then again, it's a pretty powerful solution if you want to add fields to a table without having to change the table's design.

Dr.NETjes : Xml

XML querying in SQL2000