Monday 3 September 2012

Iterating items in XML using cursor in sql server

DECLARE @CUR CURSOR
DECLARE @VAR1 VARCHAR(20)
DECLARE @XML XML

SET @XML = '
      <contacts >
         <contact>
            <names>Bob1</names>           
         </contact>
         <contact>
            <names>Bob2</names>           
         </contact>
         <contact>
            <names>Bob3</names>           
         </contact>
      </contacts>'

SET @CUR = CURSOR FOR
 SELECT  convert(VARCHAR(20),contact.ITEM.query('./names').value('.','VARCHAR(20)')) names
                                            FROM @XML.nodes('/contacts/contact') AS contact(ITEM)
                                           
OPEN @CUR
FETCH NEXT
FROM @CUR INTO @VAR1

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @VAR1
PRINT '------------------------------------------------'
FETCH NEXT
FROM @CUR INTO @VAR1
END
CLOSE @CUR
DEALLOCATE @CUR




Above is the sample used to iterating item in xml using cursor in sql server
 

No comments:

Post a Comment

Total Pageviews