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)

Comments

Posts By Year

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

Posts By Category

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

Posts By Tags

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