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
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