Parse XML Column in a Where Clause

Question

Hello All,

I have an SQL Table A with a XML Columns XMlColumn that has records such as

<row>
  <col name="XTYPE">AB</col>
  <col name="XCODE">NAME-12</col>
  <col name="X_DAY">1/1/2011 12:00:00 AM</col>
</row>
<row>
  <col name="XTYPE">AB</col>
  <col name="XCODE">NAME-12</col>
  <col name="X_DAY">2/1/2011 12:00:00 AM</col>
</row>

I would like to write a SQL Query on  Table A with a where clause on the  xml  column for e.g X_Day > Some Date.

Can someone show me how?

---

Regards, Dinesh

Answer 1

XQuery date comparisons can be a bit fiddly so you might be best to pull the value out using the .value method and compare it in SQL.  Try this demo:

DECLARE @yourTable TABLE ( rowId INT IDENTITY PRIMARY KEY, xmlColumn XML )

INSERT INTO @yourTable ( xmlColumn )
SELECT '<row>
  <col name="XTYPE">AB</col>
  <col name="XCODE">NAME-12</col>
  <col name="X_DAY">1/1/2010 12:00:00 AM</col>
</row>
<row>
  <col name="XTYPE">AB</col>
  <col name="XCODE">NAME-12</col>
  <col name="X_DAY">2/1/2010 12:00:00 AM</col>
</row>'

INSERT INTO @yourTable ( xmlColumn )
SELECT '<row>
  <col name="XTYPE">AB</col>
  <col name="XCODE">NAME-12</col>
  <col name="X_DAY">1/1/2011 12:00:00 AM</col>
</row>
<row>
  <col name="XTYPE">AB</col>
  <col name="XCODE">NAME-12</col>
  <col name="X_DAY">2/1/2011 12:00:00 AM</col>
</row>'


DECLARE @yourDate DATETIME

SET @yourDate = '1/1/2011 12:00:00 AM'

-- Only expect rowId 2 to come back...
SELECT *
FROM @yourTable t
WHERE EXISTS 
	(
	SELECT t.*
	FROM @yourTable t2
		CROSS APPLY t2.xmlColumn.nodes('row/col[@name="X_DAY"]') c(c)
	WHERE t.rowId = t2.rowId
	AND c.c.value( '.', 'DATETIME' ) > @yourDate
	)

Answer 2

wBob, you the best!

---

Regards, Dinesh

Answer 3

I should probably add that if your date values in the XML were in a proper dateTime format then you could compare them directly in the xml.  Also you can't compare use sql:variable with SQL Server datatype DATETIME in SQL 2005.

DECLARE @yourTable TABLE ( rowId INT IDENTITY PRIMARY KEY, xmlColumn XML )

-- Valid XML dateTime format is YYYY-MM-DDThh:mm:ss.sss
INSERT INTO @yourTable ( xmlColumn )
SELECT '<row>
  <col name="XTYPE">AB</col>
  <col name="XCODE">NAME-12</col>
  <col name="X_DAY">2010-01-01T00:00:00</col>
</row>
<row>
  <col name="XTYPE">AB</col>
  <col name="XCODE">NAME-12</col>
  <col name="X_DAY">2010-02-01T00:00:00</col>
</row>'

INSERT INTO @yourTable ( xmlColumn )
SELECT '<row>
  <col name="XTYPE">AB</col>
  <col name="XCODE">NAME-12</col>
  <col name="X_DAY">2011-01-01T00:00:00</col>
</row>
<row>
  <col name="XTYPE">AB</col>
  <col name="XCODE">NAME-12</col>
  <col name="X_DAY">2011-02-01T00:00:00</col>
</row>'


DECLARE @yourDate DATETIME

SET @yourDate = '1/1/2011 12:00:00 AM'


SELECT t.*, 
	c.c.query('xs:dateTime(.)') c,
	c.c.query('. > (sql:variable("@yourDate"))') y
FROM @yourTable t
	CROSS APPLY t.xmlColumn.nodes('row/col[@name="X_DAY"]') c(c)

SELECT t.*, 
	c.c.query('xs:dateTime(.)') c,
	c.c.query('.') y
FROM @yourTable t
	CROSS APPLY t.xmlColumn.nodes('row/col[@name="X_DAY"][. > (sql:variable("@yourDate"))]') c(c)

SELECT t.*
FROM @yourTable t
WHERE t.xmlColumn.exist('row/col[@name="X_DAY"][. > (sql:variable("@yourDate"))]') = 1