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:
