Mega Code Archive
Creating xml Data Type Columns and insert data
<![CDATA[
5>
6> CREATE TABLE dbo.Book(
7> BookID int IDENTITY(1,1) PRIMARY KEY,
8> ISBN char(10) NOT NULL,
9> BookName varchar(250) NOT NULL,
10> AuthorID int NOT NULL,
11> ChapterDESC XML NULL)
12> GO
1>
2> CREATE PROCEDURE dbo.usp_INS_Book
3> @ISBN char(10),
4> @BookName varchar(250),
5> @AuthorID int,
6> @ChapterDESC xml
7> AS
8> INSERT dbo.Book
9> (ISBN, BookName, AuthorID, ChapterDESC)
10> VALUES (@ISBN, @BookName, @AuthorID, @ChapterDESC)
11> GO
1>
2> --Inserting xml Data into a Column
3>
4> INSERT dbo.Book
5> (ISBN, BookName, AuthorID, ChapterDESC)
6> VALUES ('1111','SQL Server',55,
7> CAST('<Book name="SQL Server">
8~ <Chapters>
9~ <Chapter id="1"> a </Chapter>
10~ <Chapter id="2"> b </Chapter>
11~ <Chapter id="3"> c </Chapter>
12~ <Chapter id="4"> d </Chapter>
13~ <Chapter id="5"> e </Chapter>
14~ <Chapter id="6"> f </Chapter>
15~ </Chapters>
16~ </Book>' as XML))
17> GO
(1 rows affected)
1>
2>
3> select * from dbo.book
4> GO
BookID ISBN BookName
AuthorID ChapterDESC
----------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------- ----------- ------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------
1 1111 SQL Server
55 <Book name="SQL Server"><Chapters><Chapter id="1"> a </Chapter><Chapter id="2"> b </Chapter><Chapter id="3"> c </C
hapter><Chapter id="4"> d </Chapter><Chapter id="5"> e </Chapter><Chapter id="6"> f </Chapter></Chapters></Book>
(1 rows affected)
1> drop table dbo.book
2> GO
1>
]]>