Cacher is the code snippet organizer for pro developers

DECLARE @xml xml
SET @xml = '<BM>
<TEAM><teamName>Japan</teamName><exchangeId>2</exchangeId></TEAM>
<TEAM><teamName>Australia</teamName><exchangeId>2</exchangeId></TEAM>
<TEAM><teamName>China</teamName><exchangeId>2</exchangeId></TEAM>
<TEAM><teamName>Honduras</teamName><exchangeId>2</exchangeId></TEAM>
<TEAM><teamName>Iran</teamName><exchangeId>2</exchangeId></TEAM>
<TEAM><teamName>Korea Republic</teamName><exchangeId>2</exchangeId></TEAM>
<TEAM><teamName>Slovakia</teamName><exchangeId>2</exchangeId></TEAM>
<TEAM><teamName>Finland</teamName><exchangeId>2</exchangeId></TEAM>
<TEAM><teamName>Belarus</teamName><exchangeId>2</exchangeId></TEAM>
<TEAM><teamName>Mexico</teamName><exchangeId>2</exchangeId></TEAM>
<TEAM><teamName>Greece</teamName><exchangeId>2</exchangeId></TEAM>
<TEAM><teamName>Argentina</teamName><exchangeId>2</exchangeId></TEAM>
</BM>'


DECLARE @handle INT  
DECLARE @PrepareXmlStatus INT  

EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @XML  

SELECT  *
FROM    OPENXML(@handle, '/BM/TEAM', 2)  
    WITH (
		teamName VARCHAR(MAX),
		exchangeId VARCHAR(MAX)
    )  

EXEC sp_xml_removedocument @handle