XML Data type in Microsoft SQL Server 2005

Share on TwitterShare on TumblrSubmit to StumbleUponSave on DeliciousDigg This



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.

use master



IF(not exists (select * from dbo.sysdatabases where name=’myTest’))


          Create database myTest




USE 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

<CustomerAddress Id=”1″>





To insert we can use same INSERT Statement as follow

insert into Customers values (‘TestCustomer’,

‘<CustomerAddress Id=”1″>






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)[1]’, ‘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>

      after (/CustomerAddress/line3)[1]’)

    UPDATE Customers SET AddressXML.modify(‘

              replace value of (/CustomerAddress/@Id)[1] 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
    FROM   Customers
    CROSS APPLY AddressXML.nodes(‘/CustomerAddress’) as MyNodes(a)