swirl
Home Software Blog Wallpapers Webtools
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 (1)
Tags: TSQL(3) XML(1)

Load Comments

Posts By Year

2016 (6)
2017 (5)
2012 (2)
2013 (4)
2014 (2)
2015 (18)

Posts By Category

Software Engineering (1)
IIS (1)
T-SQL (1)
Git (1)
Technology (1)
Database migration (1)
PHP (1)
Corporate culture (1)
Tablet (1)
OData (1)
Containers (3)
Lucene (1)
Test Driven (1)
Java (2)
TDD (1)
Solr (1)
XML (1)
TSQL (5)
Windows (4)
PowerShell (1)
Software development (1)
Wallpapers (1)
Utilities (1)
Entity Framework (2)
Rants (5)
Life (5)
Programming (13)
Office (1)
Book review (3)
Sql Server (1)
ASP.NET MVC (4)
Android (1)
Desktop (1)
Unit Testing (1)

Posts By Tags

Dockers(2) Books(1) Organization(1) Busy(1) PowerShell(1) PHP(1) Backup(1) Database(1) Containers(3) Mobile Apps(1) Paths(1) SSH(1) Web Development(4) Yii(1) Debugging(1) Advertising(1) Adults(1) GradleApache(1) C#(2) SQL Code-first Migration(1) Developer(1) Consumerism(1) Log4J(1) Self reliance(1) Company culture(1) Entity framework(1) Work culture(1) Programming(1) Management(2) Book review(2) Validation(2) C++(1) Sql Express(1) HierarchyID(1) Lucene(1) IIS(1) Windows 10(1) Cmdlet(1) Intelli J(1) JavaScript(1) Spirituality(1) Migration history(1) Tablet(1) OData(1) TDD(1) Data-time(1) Beliefs(1) Checkbox(1) CTE(1) CSRF(1) JSON(1) Windows 8.1(1) Quality(1) XML(1) ASP.NET(4) TSQL(3) File history(1) Windows(1) Wallpapers(1) SD card(1) File copy(1) SQL(1) Windows 2016(2) Python(1) MVC(4) Url rewrite(1) Solr(1) Modern Life(1) System Image(1) Android(1) Commons(1) Office(1) Anti-forgery(1)