A simple XML parsing example

DECLARE @t TABLE ( Id INT PRIMARY KEY, booksXML XML )

INSERT INTO @t VALUES

( 1, '<books
category="novel"><book>Gone with the
wind</book><book>The lord of the rings</book></books>'
),

( 2, '<books
category="textbook"><book>linear
algebra</book><book>advanced
mathematics</book></books>' )

SELECT t.Id, x.y.value('.', 'VARCHAR(100)') book

FROM @t t

CROSS APPLY t.booksXML.nodes('books/book') x(y)