T-SQL Cursor and XML Basic Example

MSSQL Server 2005 provides native support for the XML data type, and new methods to directly parse and read the data. The following two articles discuss the MSSQL Server 2005 XML capabilities:

http://www.setfocus.com/TechnicalArticles/Articles/sql-server-2005-xml.aspx[^]

http://www.15seconds.com/Issue/050803.htm[^]

And here is a basic example:

-- xml: http://www.setfocus.com/TechnicalArticles/Articles/sql-server-2005-xml.aspx
-- XQuery: http://www.15seconds.com/Issue/050803.htm
DECLARE	@XMLText XML
SET	@XMLText = '
<Customers>
  <Customer>
    <FirstName>Kevin</FirstName>
  </Customer>
  <Customer>
   <FirstName>Steve</FirstName>
  </Customer>
</Customers>'

SELECT	@XMLText.query('/Customers/Customer/FirstName')
/* Output
<FirstName>Kevin</FirstName>
<FirstName>Steve</FirstName>
*/

Now let's assume that we have a table (#MAP) that contains an XML mapping to another table (#NODE). In order to validate the mappings, we need to check if a node referenced in an XML map exists in the #NODE table. A solution is to use a cursor. And here is a basic example of a cursor:

-- cursor: http://msdn.microsoft.com/en-us/library/ms180169.aspx
DECLARE	@tablename sysname
DECLARE	tables_cursor CURSOR FOR
	SELECT	name
	FROM	sys.objects
	WHERE	type = 'U' AND UPPER(NAME) LIKE UPPER('%%')

OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC ('SELECT Top 1 * FROM ' + @tablename)
	FETCH NEXT FROM tables_cursor INTO @tablename
END

CLOSE tables_cursor
DEALLOCATE tables_cursor

However, since cursors are the slowest way to access data inside MSSQL Server (http://www.sqlteam.com/article/cursors-an-overview[^]), the following snippet includes both the cursor and the set-based way to scroll through and validate the mappings:

CREATE TABLE #NODE
(
	ID INT IDENTITY, 
	NAME NVARCHAR(MAX)
)

INSERT INTO #NODE VALUES ('TESTNODE')

CREATE TABLE #MAP
(
	ID INT IDENTITY, 
	XML_TEXT NVARCHAR(MAX),
	IS_ENABLED BIT
)

INSERT INTO #MAP VALUES ('<MAP><INPUT><NODE><ID>1</ID></NODE></INPUT><OUTPUT>1234</OUTPUT></MAP>', 1)
INSERT INTO #MAP VALUES ('<MAP><INPUT><NODE><ID>11</ID></NODE></INPUT><OUTPUT>1234</OUTPUT></MAP>', 1)

/*
SET NOCOUNT ON  -- http://msdn.microsoft.com/en-us/library/ms189837.aspx
DECLARE @MAP_ID INT, @MAP_XML XML, @NODE_ID INT
DECLARE	map_cursor CURSOR FOR
	SELECT	ID, XML_TEXT
	FROM	#MAP
 
OPEN map_cursor
FETCH NEXT FROM map_cursor INTO @MAP_ID, @MAP_XML
 
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @NODE_ID = ISNULL(@MAP_XML.value('(//NODE/ID)[1]', 'INT'), 0)
    IF NOT EXISTS(SELECT ID FROM #NODE WHERE ID = @NODE_ID)
    BEGIN
        PRINT 'UNDEFINED NODE ID: ' + CAST(@NODE_ID AS NVARCHAR(30)) + ' IN MAP: ' + CAST(@MAP_XML AS NVARCHAR(MAX))
        UPDATE #MAP SET IS_ENABLED = 0 WHERE ID = @MAP_ID
    END 
     
    FETCH NEXT FROM map_cursor INTO @MAP_ID, @MAP_XML
END

CLOSE map_cursor
DEALLOCATE map_cursor
*/

UPDATE #MAP SET IS_ENABLED = 0
--SELECT *  
FROM #MAP M
LEFT JOIN #NODE N ON N.ID = ISNULL((CAST(M.XML_TEXT AS XML)).value('(//NODE/ID)[1]', 'INT'), 0)
WHERE N.ID IS NULL

DROP TABLE #MAP
DROP TABLE #NODE