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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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">
 <title>Inside Microsoft SQL Server 2008: T-SQL Querying</title>
 <author>Itzik Ben-Gan</author>
 <title>Microsoft SQL Server 2008 T-SQL Fundamentals</title>
 <author>Itzik Ben-Gan</author>
 <title>Advanced Transact-SQL for SQL Server 2000</title>
 <author>Itzik Ben-Gan</author>

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.


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


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.

 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

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.