snippets for tsql that deals with xml

tsql-xml.sql

-- T-SQL, XQuery, XML stuff quick ref

declare @xml xml = N'
<root>
	<node roll="1" fname="sid">Luke Skywalker</node>
	<node roll="2" fname="neel">Anakin Skywalker?<node>
</root>'

-- prints the roll number of the first node
select @xml.value('(/root/node/@roll)[1]', 'INT')

-- prints the text of the second node
select @xml.value('(/root/node)[2]', 'VARCHAR(40)')

-- prints the node text and attribute values of the node which has roll = 2
select
	xmldata.col.value('@roll', 'int'),
	xmldata.col.value('@fname', 'varchar(10)'),
	xmldata.col.value('.', 'varchar(20)')
from
	@xml.nodes('(/root/node)') xmldata(col)
where
	xmldata.col.value('@roll', 'int') = 2