XML Data Type in Microsoft SQL Server 2005

Introduction:

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

go

 

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

BEGIN

          Create database myTest

END

GO

 

USE myTest

GO

IF NOT( EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'Customers') AND

OBJECTPROPERTY(id, N'IsUserTable') = 1))

          BEGIN

 

                   CREATE TABLE Customers

                   (

                             [CustomerID] [int] IDENTITY(1,1) NOT NULL,

                             [CustomerName] [varchar](64) NULL,

                             [AddressXML] [xml] NULL

                   )

 

          END

          ELSE

          BEGIN

                   PRINT 'Customers Table already exists'

          END

GO

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">

  <line1>abc</line1>

  <line2>xyz</line2>

  <line3>123456</line3>

</CustomerAddress>

To insert we can use same INSERT Statement as follow

insert into Customers values ('TestCustomer',

'<CustomerAddress Id="1">

  <line1>abc</line1>

  <line2>xyz</line2>

  <line3>123456</line3>

</CustomerAddress>

')

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)