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
- Query(): Use this method to query over an XML instance.
SELECT AddressXML.query (‘/CustomerAddress[@Id = 1]’) FROM Customers
- Value(): Use this method to retrieve a value of SQL type from an XML instance
SELECT * FROM WHERE AddressXML.value(‘(/CustomerAddress/line1)’, ‘nvarchar(1000)’) =’abc’
- exist(): Use this method to determine whether a query returns a nonempty result.
SELECT * FROM WHERE AddressXML.exist (‘/CustomerAddress[@Id = 1]’) = 1
- Modify(): Use this method to specify XML DML statements to perform updates
UPDATE Customers SET AddressXML.modify(‘ insert <Line4>Newline</Line4>
UPDATE Customers SET AddressXML.modify(‘
replace value of (/CustomerAddress/@Id) with 100’ )
- Nodes(): Use this method to shred XML into multiple rows to propagate parts of XML documents into rowsets
SELECT AddressXML.query(‘.’)as nodes
CROSS APPLY AddressXML.nodes(‘/CustomerAddress’) as MyNodes(a)