Working with XML Data Type

The XML data type, introduced in SQL Server 2005. XML data type can store either a complete XML or a fragment of XML document. Before xml data type the xml data was stored in varchar or text data type, that was proven to be poor in terms of querying and manipulating the XML data.

When to use XML data type:

• If one wants to store the XML document in the database and retrieve and update the document as a whole - that is, if you never need to query or modify the individual XML components - you should consider using one of the large object data types like VARCHAR(MAX), NVARCHAR(MAX) or VARINARY(MAX).
• If you want to preserve the original form such as legal documents or want to retain an exact textual copy, use large object storage.
• The rest of the time, one should consider using XML data type which ensures that the data is well formed according to ISO standards, and supports fine-grained queries and modifications to specific elements and attributes within the XML. One can also index an XML column and associate its data with an XML schema collection in order to preserve its content and structure.

Limitations of using XML data type:

• This cannot be used as a key in an index
• Cannot exceed 2 GB size
• XML data type cannot be compared, sorted, or used in GROUP BY
• It does not support casting or converting to either text or ntext
• It cannot be used as a parameter to any scalar, or built-in functions other than ISNULL, COALESCE and DATALENGTH

Data Manipulations on XML column: Below illustrations explains how to SELECT, INSERT, UPDATE and DELETE data in a XML column.

Below is the sample XML used to walk through the manipulations:

<Employees>

<Employee>

<ID>1</ID>

<Name>John Test</Name>

<Department>IT</Department>

<Address>

<Street1>4404, Main Road</Street1>

<Street2/>

<City>Reston</City>

<State>VA</State>

<Zip>20171</Zip>

</Address>

</Employee>

<Employee>

<ID>2</ID>

<Name>King George</Name>

<Department>Admin</Department>

<Address>

<Street1>3004, St. Patric Road</Street1>

<Street2>Apt D</Street2>

<City>Vienna</City>

<State>VA</State>

<Zip>20171</Zip>

</Address>

</Employee>

</Employees>

SELECTing Employee Name, Department and Address for the given Employee ID

SELECT

t.c.value('Name[1]', 'varchar(max)') as 'Employee Name',

t.c.value('Department[1]', 'varchar(max)') as 'Department',

t1.c1.value('Street1[1]', 'varchar(max)') + ', ' +

t1.c1.value('Street2[1]', 'varchar(max)') +

-- Conditionalyy append comma based on Street2 value, Append when Street2 available else don't

case when ((t1.c1.value('Street2[1]', 'varchar(max)') != '')) then (', ') else ('') end +

t1.c1.value('City[1]', 'varchar(max)') + t1.c1.value('State[1]', 'varchar(max)') + ' - ' +

t1.c1.value('Zip[1]', 'varchar(max)') as 'Address'

FROM

@XML.nodes('//Employees/Employee') as t(c)

CROSS APPLY t.c.nodes('Address') as t1(c1)

WHERE

t.c.value('ID[1]', 'int') = 1

UPDATing Street2 of Address for the given Employee ID

SET @employeeID = 2 -- Provide employee ID
-- Check for provided employee ID and if Street2 value is NULL
IF @Xml.exist('/Employees/Employee/ID[text()=sql:variable("@employeeID")]/../Address/Street2/text()') = @employeeID

BEGIN -- will execute for employeeID = 2

PRINT 'First Part'

SET
@Xml.modify('replace value of (/Employees/Employee/ID[text()=sql:variable("@employeeID")]/../Address/Street2/text())[1] with "Apt F"')

END

ELSE -- IF Street2 tag value is present execute below

BEGIN -- will execute for employeeID = 1

PRINT 'Second Part'

SET @Xml.modify('delete (/Employees/Employee/ID[text()=sql:variable("@employeeID")]/../Address/Street2)')

SET @Xml.modify('insert (<Street2>Apt A</Street2>) after (/Employees/Employee/ID[text()=sql:variable("@employeeID")]/../Address/Street1)[1]')

END

SELECT @Xml

DELETing Street2 of Address for the given Employee ID

SET @employeeID = 1 -- Provide employee ID

BEGIN

SET @Xml.modify('delete (/Employees/Employee/ID[text()=sql:variable("@employeeID")]/../Address/Street2)')

END

SELECT @Xml

INSERTing Street3 after Street2 of Address for the given Employee ID

SET @employeeID = 1 -- Provide employee ID

BEGIN

SET @Xml.modify('insert (<Street3>Near</Street3>) after (/Employees/Employee/ID[text()=sql:variable("@employeeID")]/../Address/Street2)[1]')

END

SELECT @Xml