Tuesday, August 25, 2015

how to get xml node value in sql server 2008

How to get XML node value in SQL server 2008


If we have an xml data into sql server and want to retrieve xml Node value as below,

XML Data into SQL Server Column as below,

<Employee>
         <EmployeeNumber>834401</EmployeeNumber>
         <Department>Insurance</Department>
</Employee>

Retrieve value as below,

SELECT 
[xmlColumn].value('(/Employee//EmployeeNumber/node())[1]','nvarchar(max)')as EmployeeNumber,

[xmlColumn].value('(/Employee//Department/node())[1]', 'nvarchar(max)') 
as Department
 
FROM [TableName]

Here [xmlColumn] = “Name of xml column of table”


And [TableName] = “Name of table”

No comments:

Post a Comment