SQL Server and XML Datatype

MSSQL supports since version 2005 XML Data. In this easy example you see how you can access the Data inside the XML Column.

Here the Table Definition:

CREATE TABLE [dbo].[person](
	[id] [uniqueidentifier] NOT NULL,
	[firstname] [varchar](50) NOT NULL,
	[lastname] [varchar](50) NOT NULL,
	[data] [xml] NULL
) 

I added a new Row with following values:

id = 0e638042-df69-4187-a112-027fa71f1578
firstname = Peter
lastname = Young
data = 

 MyUsername
 My secure clear text password
 
  2010-12-24 20:00:00
  2010-12-24 20:05:00
 

The following query reads some Table Fields + some Data out of the XML.

SELECT	
id,
firstname,
lastname, 
Person.value('(Username/text())[1]', 'varchar(30)') Username, 
Person.value('(Password/text())[1]', 'varchar(30)') [Password]
FROM person
CROSS APPLY data.nodes('//PersonData') t1(Person)

Result Recordset:

And the following SELECT reads the Login-Child Elements.

SELECT	
id,
firstname,
lastname, 
Person.value('(Username/text())[1]', 'varchar(30)') Username, 
Person.value('(Password/text())[1]', 'varchar(30)') [Password],
PersonLogin.value('(text())[1]', 'datetime') as logintime
FROM person
CROSS APPLY data.nodes('//PersonData') t1(Person)
CROSS APPLY Person.nodes('Login/Time') t2(PersonLogin)

The Recordset will now contain two rows, because we have two Child Elements inside “Login”.

Result Recordset:

The XML Datatype can be helpful when you need more flexibility or a kind of ‘generic support’.