SQL XML Query in SQL Server 2008
With t-sql enhancements introduced with Microsoft SQL Server 2005, developers are now enable to use more powerful T-SQL XML commands during their developments.
Let's define a sample sql xml variable @xml using type definition XML.
Our sample SQL XML data contains a list of SQL Server 2008 books published by various publishing companies.
The books are categorized in the XML structure according to their place in the SQL Server Data Platform like SSRA, SSAS and BI.
SQL developers will see the root node "books" and just under root node sql developers will realize the "category" xml node with "type" attribute.
Under the "category" sql xml node the books are listed.
DECLARE @xml AS XML = N'
<books>
<category type="ssrs">
<book lang="en">Microsoft SQL Server 2008 Reporting Services Step by Step</book>
<book lang="en">Microsoft SQL Server 2008 Reporting Services</book>
<book lang="en">Applied Microsoft SQL Server 2008 Reporting Services</book>
<book lang="en">Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports</book>
</category>
<category type="ssas">
<book lang="en">Microsoft SQL Server 2008 Analysis Services Step by Step</book>
<book lang="en">Microsoft SQL Server 2008 MDX Step by Step</book>
<book lang="en">Expert Cube Development with Microsoft SQL Server 2008 Analysis Services</book>
</category>
<category type="bi">
<book lang="en">Smart Business Intelligence Solutions with Microsoft SQL Server 2008</book>
<book lang="en">Delivering Business Intelligence with Microsoft SQL Server 2008</book>
</category>
</books>
'
SELECT @xml
If t-sql developers click on the output of the SELECT statement, @xml variable value, the XML output will be displayed on the SQL Server Management Studio XML Editor window as follows:
This is the sample SQL XML data which sql developers will use in each t-sql xml query given in the following section of this MSSQL XML tutorial.
SQL developers or administrators can download sample SQL Server 2008 books XML data using this link.
If you are a SQL developer who wants to get the list of SQL Server books from the above XML document, you can use a T-SQL XML SELECT query shown below.
In the below t-sql SELECT statement, you will realize that the FROM part is different from usual sql syntax.
In the FROM clause, we point to a table which is formed of XML nodes
Those XML nodes are selected with a hierarchical structure as '/books/category/book/'.
As you see I named the table as Books which is formed of a column named "[Book Name]".
SELECT
[Book Name].value('.','varchar(100)') AS Book
FROM @xml.nodes('/books/category/book') Books([Book Name])
Within the above SQL XML script, the SELECT part is also showing differences from a usual T-SQL Select statement.
I select the XML node named [Book Name], fetch its inner value with "." notation and convert that XML data to VARCHAR value.
And the output of the above t-sql SELECT from XML data query is as shown in the below screenshot.
If we request for more information about each book from t-sql developers, like the language of the SQL books and the category of each SQL Server book within the xml import list, how we can alter t-sql xml query?
Let's check the below sql xml query which can be used to select the xml attribute of each node and get values and attributes from upper hierarcy nodes.
SELECT
[Book Name].value('.','varchar(100)') AS Book,
[Book Name].value('@lang','varchar(100)') AS [Language],
[Book Name].value('../@type','varchar(100)') AS Category
FROM @xml.nodes('/books/category/book') Books([Book Name])
The SELECT XML query will result with the below output.
In this short SQL Server XML tutorial, we build an SQL XML query to get a list of XML node items as query output column.
If your XML data source is outside SQL Server 2008, you can read sql tutorial How to Import XML into SQL Server 2005 or SQL Server 2008 for sample xml import t-sql queries.