Sql Server: Select or Read XML Column Data from table

Introduction:  In this article I am going to share how to parse and read data from XML data type column of sql server table.

In previous article i explained How to Update multiple xml node values with new values in table and Parse xml and get data as comma separated string format and Read or parse xml data and insert into sql table and Delete or select first or last n records from table and Find all primary and foreign key constraints on each or any table in database

Implementation: Let’s create an employee table using table variable for demonstration purpose and insert some dummy data along with xml data in xml type column of table.

DECLARE @Employee TABLE(EmployeeId INT, Age INT, empXML XML)

INSERT INTO @Employee(EmployeeId, Age, empXML) VALUES(101,22,'<ROOT><Employee Country="India" State="Haryana" City="Hissar" /></ROOT>')

INSERT INTO @Employee(EmployeeId, Age, empXML) VALUES(102,25,'<ROOT><Employee Country="India" State="Punjab" City="Patiala" /></ROOT>')

INSERT INTO @Employee(EmployeeId, Age, empXML) VALUES(103,32,'<ROOT><Employee Country="India" State="Maharashtra" City="Mumbai" /></ROOT>')

INSERT INTO @Employee(EmployeeId, Age, empXML) VALUES(104,21,'<ROOT><Employee Country="India" State="Himachal" City="Shimla" /></ROOT>')

Check table data

SELECT * FROM @Employee

Now the query to read data from XML column and select as table will be as:

SELECT EmployeeId, Age,

col.value('@Country','VARCHAR(100)') AS Country,

col.value('@State','VARCHAR(100)') AS State,

col.value('@City','VARCHAR(100)') AS City

FROM @Employee

CROSS APPLY empXML.nodes('ROOT/Employee') tab(col);

Check updated table data now

SELECT * FROM @Employee

Result will be as shown in image above.

  Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 

• Tweet
• Share
• Share
• Share

Related Post