SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Books(XML xml);
INSERT Books VALUES (N'<Books>
 <Book>
  <Title>Apology</Title>
  <Author>Socrates</Author>
 </Book>
 <Book>
  <Title>Republic</Title>
  <Author>Plato</Author>
 </Book>
 <Book>
  <Title>Politics</Title>
  <Author>Aristotle</Author>
 </Book>
</Books>
');
1 rows affected

SELECT 
  x1.Book.value('(Title/text())[1]', 'VARCHAR(100)') AS Title,
  x1.Book.value('(Author/text())[1]', 'VARCHAR(100)') AS Author
FROM dbo.Books b
CROSS APPLY b.XML.nodes('Books/Book') AS x1 (book);


Title
Author


Apology
Socrates

Republic
Plato

Politics
Aristotle


SELECT 
  x2.Title.value('text()[1]', 'VARCHAR(100)') AS Title,
  x3.Author.value('text()[1]', 'VARCHAR(100)') AS Author
FROM dbo.Books b
CROSS APPLY b.XML.nodes('Books/Book') AS x1 (Book)
CROSS APPLY x1.Book.nodes('Title') AS x2 (Title)
CROSS APPLY x1.Book.nodes('Author') AS x3 (Author);


Title
Author


Apology
Socrates

Republic
Plato

Politics
Aristotle