Declare XML type variable and use it in insert statement
3>
4>
5> CREATE TABLE dbo.Book(
6> BookID int IDENTITY(1,1) PRIMARY KEY,
7> ISBN char(10) NOT NULL,
8> BookName varchar(250) NOT NULL,
9> AuthorID int NOT NULL,
10> ChapterDESC XML NULL)
11> GO
1> CREATE PROCEDURE dbo.usp_INS_Book
2> @ISBN char(10),
3> @BookName varchar(250),
4> @AuthorID int,
5> @ChapterDESC xml
6> AS
7> INSERT dbo.Book
8> (ISBN, BookName, AuthorID, ChapterDESC)
9> VALUES (@ISBN, @BookName, @AuthorID, @ChapterDESC)
10> GO
1> DECLARE @Book XML
2> SET @Book =
3> CAST('<Book name="SQL Server">
4~ <Chapters>
5~ <Chapter id="1"> chapter 1 </Chapter>
6~ <Chapter id="2"> chapter 2 </Chapter>
7~ <Chapter id="3"> chapter 3 </Chapter>
8~ <Chapter id="4"> chapter 4 </Chapter>
9~ </Chapters>
10~ </Book>' as XML)
11>
12> INSERT dbo.Book
13> (ISBN, BookName, AuthorID, ChapterDESC)
14> VALUES ('1111111','SQL Server',55,@Book)
15> GO
(1 rows affected)
1> select * from dbo.book
2> GO
BookID ISBN BookName
AuthorID ChapterDESC
----------- ---------- -------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--------------------------------- ----------- --------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------
1 1111111 SQL Server
55 <Book name="SQL Server"><Chapters><Chapter id="1"> chapter 1 </Chapter><Ch
apter id="2"> chapter 2 </Chapter><Chapter id="3"> chapter 3 </Chapter><Chapter id="4"> chapter 4 </Chapter></Chapters><
/Book>
(1 rows affected)
1>
2> drop table dbo.book
3> GO
Related examples in the same category