-- Create the BOOK table
CREATE TABLE BOOK (
    Book_id INT PRIMARY KEY,
    Title VARCHAR(255),
    Author_Name VARCHAR(255),
    Publisher_Name VARCHAR(255),
    Pub_Year INT,
    No_of_Copies INT
);
GO

-- Insert dummy data into the BOOK table
INSERT INTO BOOK (Book_id, Title, Author_Name, Publisher_Name, Pub_Year, No_of_Copies)
VALUES (1, 'Book Title 1', 'Author 1', 'Publisher 1', 2024, 5),
       (2, 'Book Title 2', 'Author 2', 'Publisher 2', 2023, 3),
       (3, 'Book Title 3', 'Author 3', 'Publisher 3', 2022, 4);
GO

-- Convert the BOOK table data into XML format
SELECT * FROM BOOK
FOR XML AUTO;

SELECT * FROM BOOK
FOR XML PATH;

SELECT * FROM BOOK
FOR XML PATH ('Book');

SELECT * FROM BOOK
FOR XML PATH ('Book'), ROOT('Library');

SELECT  Book_id as [@BookID],  
    Title AS [BookInfo/Title],  
    Author_Name AS [BookInfo/Author],  
    Publisher_Name AS [BookInfo/Publisher],  
    Pub_Year AS [BookInfo/Year],  
    No_of_Copies AS [BookInfo/Copies]
FROM BOOK 
FOR XML PATH ('Book'), ROOT('Library');

-- Assuming you have XML content related to the BOOK table
DECLARE @xml XML
SET @xml = '<Library><Book BookID="1"><BookInfo><Title>Book Title 1</Title><Author>Author 1</Author><Publisher>Publisher 1</Publisher><Year>2024</Year><Copies>5</Copies></BookInfo></Book><Book BookID="2"><BookInfo><Title>Book Title 2</Title></BookInfo></Book></Library>'

DECLARE @hdoc INT
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

SELECT *
INTO NewBooks
FROM OPENXML(@hdoc, '/Library/Book')
WITH (
    BookID INT '@BookID',
    Title VARCHAR(255) 'BookInfo/Title',
    Author VARCHAR(255) 'BookInfo/Author',
    Publisher VARCHAR(255) 'BookInfo/Publisher',
    Year INT 'BookInfo/Year',
    Copies INT 'BookInfo/Copies'
)

EXEC sp_xml_removedocument @hdoc

SELECT * FROM NewBooks;