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:
|