SQL Server 2005 supports a new data type named xml. The xml type can store either a complete XML document, or a fragment of XML, as long as it is well-formed prior to SQL Server 2005, developers often used VARCHAR or TEXT column types to store XML documents and fragments. Although this approach served well as far as data storage is concerned, it proved to be poor in terms of querying and manipulating the XML data.
Create a table in an SQL Server database that contains a column of type XML.
IF(not exists (select * from dbo.sysdatabases where name='myTest'))
Create database myTest
IF NOT( EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'Customers') AND
OBJECTPROPERTY(id, N'IsUserTable') = 1))
CREATE TABLE Customers
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](64) NULL,
[AddressXML] [xml] NULL
PRINT 'Customers Table already exists'
The table has three columns: CustomerID , CustomerName and AddressXML. The CustomerID column is an identity column and acts as the primary key column. The AddressXML column is of type XML. Choosing the data type as XML will allow us to store and retrieve XML documents or fragments in this column.
Storing XML DATA
Assume that you have an XML fragment, as shown below
To insert we can use same INSERT Statement as follow
insert into Customers values ('TestCustomer',
This will create a record for 'TestCustomer' having address line1='abc' line2='xyz' and line3='123456'.
XML Data Type Methods