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, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




Install SQL Server 2019
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



SQL Server XML Query Example for Database Developer


In this SQL XML Query example, T-SQL developers will see SQL XML enhancements introduced first with Microsoft SQL Server 2005 related with SQL XML features like XML data type, XML queries.

A SQL database developer who is good at understanding and querying XML data for filtering out required information will easily query JSON data too. On this SQL tutorial as database programmer we will focus on SQL XML query, to query JSON on SQL Server please refer to given tutorial link.

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

<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 machine on root of C drive with the name sql-books.xml

Second step for sql developers will be reading XML file using sql code 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 database developer 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 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems