XML.sql

-- xml retrieval
declare @xml xml
SET @xml = '<Categories>
			  <Category ID="4">
				<name>Accessories</name>
			  </Category>
			  <Category ID="1">
				<name>Bikes</name>
			  </Category>
			  <Category ID="3">
				<name>Clothing</name>
			  </Category>
			  <Category ID="2">
				<name>Components</name>
			  </Category>
			  <Category ID="5">
				<name>Something</name>
			  </Category>
			</Categories>'

-- Option 1
declare @documentHandle int
exec sp_xml_preparedocument @documentHandle out, @xml
select * from openxml(@documentHandle, '/Categories/Category',1)
with(
	Name		varchar(50)		'name',
	CategoryID	int				'@ID' 
)

-- Option 2
SELECT script.Item.value('@ID','int') FirstName,
       script.Item.query('./name').value('.','VARCHAR(250)') Name
FROM @xml.nodes('/Categories/Category') AS script(Item)