SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014



Microsoft SQL Server XML Query Example


In this SQL XML Query example, t-sql developers will see enhancements introduced first with Microsoft SQL Server 2005 related with SQL XML features.

Let's start first by defining a sample sql xml structure which contains a list of SQL programming and administration books.

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<books>
 <id>1</id>
 <title>Inside Microsoft SQL Server 2008: T-SQL Querying</title>
 <author>Itzik Ben-Gan</author>
 <url>http://www.microsoft-press.co.uk/scripts/product.asp?ref=910532</url>
 <language>English</language>
</books>
<books>
 <id>2</id>
 <title>Microsoft SQL Server 2008 T-SQL Fundamentals</title>
 <author>Itzik Ben-Gan</author>
 <url>http://www.microsoft-press.co.uk/scripts/product.asp?ref=891577</url>
 <language>English</language>
</books>
<books>
 <id>3</id>
 <title>Advanced Transact-SQL for SQL Server 2000</title>
 <author>Itzik Ben-Gan</author>
 <url>http://apress.com/book/view/9781893115828</url>
 <language>English</language>
</books>
</root>

Now, sql developers can save this XML file on SQL Server root of C drive with the name sql-books.xml



Second step for sql developers will be reading XML file from sql and import xml data into an sql xml variable.

Not let's define the variable @xml as XML sql data type.
Then import XML data using SQL OPENROWSET command.

DECLARE @xml XML

SELECT
 @xml = books
FROM OPENROWSET (BULK 'c:\sql-books.xml', SINGLE_BLOB) AS ImportXML(books)

SELECT @xml

SQL developers can now build sql select statements which returns the list of sql books by reading XML data.
Here is a sql xml example query.

SELECT
 Book.value('id[1]','varchar(5)') AS Book,
 Book.value('title[1]','varchar(100)') AS Title,
 Book.value('author[1]','varchar(100)') AS Author,
 Book.value('url[1]','varchar(100)') AS URL,
 Book.value('language[1]','varchar(100)') AS Language
FROM @XML.nodes('root/books') Books([Book])

The t-sql XML query output is as follows in the following screenshot:

sql-xml-rowset-query






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems