Xml and TSQL useful code snippets

Sunday 01, February 2015   |   Post link

Every new version of SQL Server gives us developers new capabilities which is cool but difficult to remember.

I never can seem to remember any SQL code I write which deals with XML. So as a reference, here is some stuff which I used recently.

-- T-SQL, XQuery, XML stuff which I never remember

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

Categories: TSQL (5) XML (2)

Tags: TSQL(3) XML(1)