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>'
),
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>' )
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)
No comments:
Post a Comment