Click here to Skip to main content
11,489,693 members (72,018 online)
Click here to Skip to main content

Tagged as

T-SQL Cursor and XML Basic Example

, 5 Apr 2010 CPOL 11.4K 5
Rate this:
Please Sign up or sign in to vote.
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:...
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

    License

    This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Share

    About the Author

    Ilka Guigova
    Software Developer
    Canada Canada

    Comments and Discussions

     
    -- There are no messages in this forum --
    | Advertise | Privacy | Terms of Use | Mobile
    Web02 | 2.8.150520.1 | Last Updated 5 Apr 2010
    Article Copyright 2010 by Ilka Guigova
    Everything else Copyright © CodeProject, 1999-2015
    Layout: fixed | fluid