Posts Tagged ‘TSQL’

Cheet sheet XML Features SQL Server

Pre-requirement: Knowledge of XPath

All the samples are based on this XML:

<MyBooks>
	<ListOwner>John Smith</ListOwner>
	<Book>
		<title published="1885">The Adventures of Huckleberry Finn</title>
		<author realName="Samuel Langhorne Clemens">Mark Twain</author>
	</Book>
	<Book>
		<title published="1894">Tom Sawyer Abroad</title>
		<author realName="Samuel Langhorne Clemens">Mark Twain</author>
	</Book>
</MyBooks>

Extract Values
select @xml.value(‘( /MyBooks/ListOwner )[1]’, ‘varchar(20)’);

Possible error:

Check if exists

select @xml.exist(‘ //Book[author/@realName=”Samuel Langhorne Clemens”] ‘)
select @xml.exist(‘ //Book[contains((author/@realName)[1],”Langhorne”)] ‘)

Extract Node

SELECT @xml.query(‘/MyBooks/Book’) –Results all book nodes.SELECT @xml.query(‘/MyBooks/Book[title=”The Adventures of Huckleberry Finn”]’)SELECT @xml.query(‘/MyBooks/Book[title/@published<1890]’)

Select nodes

This will allow you to run SQL queries on XML.

select t.c.value(‘(title)[1]’,’varchar(20)’) from @xml.nodes(‘ //Book ‘) as t(c)

Modify Xml

Namespaces:

When there are namespaces in you XML you will have to do additional work.