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.

install SQL Server 2019
download SQL Server 2017
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">
 <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 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.


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


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.

 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:


Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

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