XML Type in SQL Server

XML is present in every sort of developments today. Knowingly or unknowingly, every application deals with XML in one way or another. For example, .NET applications use XML files to store configuration information. ASP.NET web pages are XML documents. Almost all modern websites generate and publish information as XML feeds (RDF, RSS, ATOM, OPML, etc.)

In this post we are going to check the XML data type introduced with SQL Server 2005 and improved in SQL Server 2008

The table definition with XML data type is as follows

     
	CREATE TABLE C_EMPLOYEES_PROJECTS
    (
        IdEmployee    INT NOT NULL,
        IdProject   INT NOT NULL,
        ProjectDetails XML,
        
        CONSTRAINT PK_EMP_PROJECTS
            PRIMARY KEY (IdEmployee, IdProject),
        CONSTRAINT FK_EMPLOYEES
            FOREIGN KEY (IdEmployee)
            REFERENCES C_EMPLOYEES(IdEmployee),
        CONSTRAINT FK_PROJECTS
            FOREIGN KEY (IdProject)
            REFERENCES C_PROJECTS(IdProject)
    )
    GO
    

Now we will add a couple of records to the table

     
INSERT INTO C_EMPLOYEES_PROJECTS 
    VALUES (1, 1, 
        '<Project Title="Migration of web application">
            <AssignmentDate>10-10-2012</AssignmentDate>
            <DueDate>04-01-2013</DueDate>
            <Technology>.NET</Technology>
            <CustomerId>4</CustomerId>
        </Project>')

    INSERT INTO C_EMPLOYEES_PROJECTS 
    VALUES (2, 4, 
        '<Project Title="Applets for intranet">
            <AssignmentDate>11-20-2012</AssignmentDate>
            <DueDate>05-01-2013</DueDate>
            <Technology>Java</Technology>
            <CustomerId>3</CustomerId>
        </Project>')   
        

The result of a select statement is the next

XQuery is a language for querying XML data that allows navigational access based on XPath 2.0
The XML data type supports the next methods:

- value(): is useful for extracting scalar values from XML documents as a relational value. This method takes an XQuery expression that identifies a single node and the desired SQL type to be returned

- query(): takes an XQuery expression that evaluates to a list of XML nodes and allows the user to extract fragments of an XML document

- exist(): allows the user to perform checks on XML documents to determine if the result of an XQuery expression is empty or nonempty. The result of this method is 1 if the XQuery expression returns a nonempty result, 0 if the result is empty, and NULL if the XML instance itself is NULL

- modify(): can be used to modify the content of an XML document. It accepts XML DML statements to insert, update, or delete one or more nodes from an XML instance

- nodes(): accepts an XQuery expression and returns a rowset in which each row represents a context node identified by the query expression. Methods of the XML data type such as query(), value(), exist(), and nodes() can also be invoked on the context nodes returned from the nodes() method

Below we present examples of the usage of each method

     
-- Examples of value() 
-- gets the value of the Title attribute of Project node 
SELECT
ProjectDetails.value('/Project[1]/@Title', 'VARCHAR(50)') AS ProjectTitle
FROM C_EMPLOYEES_PROJECTS

-- gets the value of the Title attribute of Project node
SELECT
ProjectDetails.value('(/Project/DueDate)[1]', 'VARCHAR(50)') AS [Due Date]
FROM C_EMPLOYEES_PROJECTS

-- You can also include xquery expressions in WHERE clause
SELECT IdEmployee
FROM C_EMPLOYEES_PROJECTS
WHERE ProjectDetails.value('(/Project/@Title)[1]', 'VARCHAR(50)') LIKE 'Applets%'

SELECT IdEmployee
FROM C_EMPLOYEES_PROJECTS
WHERE ProjectDetails.value('(/Project/Technology)[1]', 'VARCHAR(50)') = '.NET'

--
-- Examples of query()

-- gets the specified xml fragment of the document
SELECT
ProjectDetails.query('/Project/DueDate') AS [Due Date]
FROM C_EMPLOYEES_PROJECTS 

-- gets the text value of the node in the specified xml framgment of the document
SELECT
ProjectDetails.query('/Project/DueDate/text()')  AS [Due Date]
FROM C_EMPLOYEES_PROJECTS 

--
-- Examples of exist()

-- will iterates the Project childnodes and returning 0 / 1 for not exist / exist respectively
SELECT
ProjectDetails.exist('/Project[1]/CustomerId[text() = "4"]')
FROM C_EMPLOYEES_PROJECTS 

--
-- Examples of modify()

-- sets the value of CustomerId node to "2" where idproject = 1
UPDATE C_EMPLOYEES_PROJECTS 
SET ProjectDetails.modify('replace value of (/Project/CustomerId/text())[1] with xs:string("2")')
WHERE IdProject = 1

-- sets the value of Title attribute where idproject = 1
UPDATE C_EMPLOYEES_PROJECTS 
SET ProjectDetails.modify('replace value of (/Project/@Title)[1] with xs:string("Migration of application and database")')
WHERE IdProject = 1

--
-- Example of nodes()

-- gets the value of the AssignmentDate node specified in the xquery expression of nodes function
SELECT P.A.query('text()')
FROM C_EMPLOYEES_PROJECTS
CROSS APPLY ProjectDetails.nodes('/Project/AssignmentDate') as P(A)

-- gets the value of thee Title attribute from the node specified in the xquery expression of nodes function
SELECT P.A.value('@Title', 'VARCHAR(50)')
FROM C_EMPLOYEES_PROJECTS
CROSS APPLY ProjectDetails.nodes('/Project') as P(A)

Finally we will apply a JOIN clause between the value of an xml node with a Relational table

     
SELECT
    EP.ProjectDetails.value('/Project[1]/@Title', 'VARCHAR(50)') 
    AS [Project Title], C.Name
FROM C_EMPLOYEES_PROJECTS EP  
CROSS APPLY ProjectDetails.nodes('/Project/CustomerId') as P(C)
JOIN C_CUSTOMERS C
ON P.C.value('.', 'INT') = C.IdCustomer

<METHOD SOFTWARE © 2013>