Querying from XML datatype table column in SQL Server

By Anil Kumar Reddy Perugu, published on 26 Sep 2011 Category: SQL SERVER 2005, 2008

Simple and best way to query XML data column in SQL Server is to us XQuery .value, .nodes, .query and .exists methods.

Introduction

Simple and best way to query XML data column in SQL Server is to us XQuery .value, .nodes, .query and .exists methods.

Let us see a simple example to select from XML data column using .nodes XQuery function and CROSS APPLY statement.

Example
Create a simple table with columns EmpId, EmpName and XML data column Dependents to store first name and last name of one or more dependents.

CREATE TABLE Employees ( EmpId INT, EmpName varchar(20), Dependents XML )

INSERT INTO Employees (EmpId,EmpName, Dependents )
SELECT 1,'Stuart',
'<Dependents>
<Name>
  <First>Stephen</First>
  <Last>Scoop</Last>
 </Name>
 <Name>
  <First>Jim</First>
  <Last>Graves</Last>
</Name>
<Name>
  <First>Philip</First>
  <Last>McIntyre</Last>
 </Name>
 </Dependents>'

Now in order to query list of employees along with their dependents use .nodes and Cross apply like below.

SELECT EmpId,EmpName,n.l.value('First[1]','VARCHAR(20)') AS DependentFirstName,
	 n.l.value('Last[1]','VARCHAR(20)') AS DependentLastName
FROM Employees
CROSS APPLY Dependents.nodes('//Name') n(l)

Result:

------------

EmpId EmpName DependentFirstName DependentLastName

----------- -------------------- -------------------- --------------------

1 Stuart Stephen Scoop

1 Stuart Jim Graves

1 Stuart Philip McIntyre

(3 row(s) affected)

Related tags

XML Datatype, SQL Server