XML Data Type in Microsoft SQL Server

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 if it is well-formed. Before SQL Server 2005, developers often used VARCHAR or TEXT column types to store XML documents and fragments. Although this approach served well regarding data storage, it proved to be poor in terms of querying and manipulating the XML data.

Create a table in an SQL Server database containing a type XML column.

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 is 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 the 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' with 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()- This method is used 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)

Conclusion

This article taught us about XML Data Type and its different types of methods with the code examples in Microsoft SQL Server.