USE pubs
GO

DECLARE @h int
DECLARE @xmldoc VARCHAR(1000)
 
SET @xmldoc = 
'<root>
<stores stor_id="8888" stor_name="Bob''s Books"  
     stor_address="111 Somewhere" city="Portland" 
     state="OR" zip="97225">
</stores> 
<stores stor_id="8889" 
     stor_name="Powell''s City Of Books"  
     stor_address="1005 W Burnside" city="Portland" 
     state="OR" zip="97209">
</stores> 
</root>'

EXEC sp_xml_preparedocument @h OUTPUT, @xmldoc

INSERT INTO stores
SELECT * FROM OpenXML(@h,'/root/stores')
WITH stores

EXEC sp_xml_removedocument @h   
