XML SQL Queries

complex_nodes_query.sql

WITH 
	PeopleAndSkills AS (
			SELECT P.*, 
				cast(Skill.query('./text()') as varchar)	as SkillName,	
				Skill.value('(./@level)[1]' ,'varchar(20)')	as SkillLevel
			FROM People P CROSS APPLY SkillsXml.nodes('/skills/skill') as Skills(Skill)
	)
SELECT * from PeopleAndSkills
WHERE SkillLevel='Medium'

cross_apply.sql

SELECT P.*, Skill.query('.')  
FROM People P CROSS APPLY SkillsXml.nodes('/skills/skill') as Skills(Skill)

Query1.sql

SELECT id,name, SkillsXml.query('/skills/skill[1]')
FROM People

Tables.sql

CREATE TABLE People (
	id	int primary key,
	name	varchar(20),
	SkillsXml xml
);

INSERT INTO People(id,name,SkillsXml) VALUES(1,'Orlando',
		'<skills>
			<skill level="High">SQL</skill>
			<skill level="Medium">C#</skill>
		</skills>');
INSERT INTO People(id,name,SkillsXml) VALUES(2,'Lina',
		'<skills>
			<skill level="Low">SQL</skill>
			<skill level="Medium">Java</skill>
		</skills>');

value.sql

SELECT id,name, SkillsXml.value('(/skills/skill[1]/@level)[1]','varchar(10)')
FROM People