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.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



Query SQL Server Database Table XML data using CROSS APPLY

SQL programmers can SELECT from XML data nodes stored in SQL Server table column with XML data type. Although it is very similar to query XML variable, querying XML data column requires the use of SQL CROSS APPLY join. Cross Apply enables to execute SQL XML query on all rows of the database table instead of running SQL Select query on single XML data.

In this SQL Server XML tutorial, I tried to explain and demonstrate to build SQL Select statement to list XML nodes with their text and attribute values stored in SQL Server sample database table column with the help of CROSS APPLY.

Insert Sample XML Data to SQL Server Table

Let's start. First create SQL Server table which has an XML data column to store XML data. My sample sql table name is Books and it has BooksXML data column with XML data type.

CREATE TABLE Books(
 Category varchar(50),
 BooksXML XML
)

Now, SQL developers are ready to populate Books sql table with sample data. I used an XML data type variable @XMLSQL within INSERT commands to populate database table with sample data as follows.

DECLARE @XMLSQL XML
SET @XMLSQL = '
<root>
<book category="Programming">SQL Server 2008 Internals</book>
<book category="Programming">T-SQL Programming</book>
<book category="Business Intelligence">Microsoft SQL Server 2008 Reporting Services</book>
<book category="Administration">Professional Microsoft SQL Server 2012 Administration</book>
</root>
'
Insert Into Books(Category,BooksXML) Select 'SQL Server', @XMLSQL
SET @XMLSQL = '
<root>
<book category="MVC">Pro ASP.NET MVC 4</book>
<book category="C#">C# 4.0 in a Nutshell</book>
<book category="CLR">CLR via C#</book>
</root>
'
Insert Into Books(Category,BooksXML) Select 'Microsoft.NET Framework', @XMLSQL
SET @XMLSQL = '
<root>
<book category="WP8">Essential Windows Phone 8</book>
<book category="WP8">Windows Phone 8 Development Internals</book>
</root>
'
Insert Into Books(Category,BooksXML) Select 'Windows Phone 8', @XMLSQL

Now in our sample database table Books, we have 3 rows. Each row stores related books data in XML format in table column BooksXML defined in XML data type.


Display XML Data to SQL Server Management Studio

If you query sample XML SQL table using a basic SQL SELECT statement, we won't be able to retrieve book details stored in XML data column. Here is a Select statement without using any SQL XML notation.

SELECT * FROM Books

SQL XML Select query with sample XML data


Convert XML to SQL Data Rows

If SQL programmer wants to read single row XML column, the SQL XML sample tutorial Query XML Data in SQL Server can be helpful. The XMl data column value can be read into XML variable, and this SQL Server XML variable can be queried for its nodes and XML attributes as follows.

DECLARE @SQLServerXML XML
SELECT @SQLServerXML = BooksXML FROM Books WHERE Category = 'SQL Server'

SELECT
 data.value('.','varchar(50)') as Book
FROM @SQLServerXML.nodes('/root/book') SQLXML(data)

XML query sample on SQL Server 2012

SQL programmers can also check the SQL Server XML tutorial SQL Server XML query example for an alternative sample case.


Convert XML Table Column to SQL Data Rows using Cross Apply

Of course this SELECT is not the solution T-SQL developers are seeking for listing all book titles seperately included in XML data in SQL table XML data columns. To SELECT from a XML type SQL table column is more complex. But SQL programmers can query XML data column using SQL Server CROSS APPLY join.

Here is a sample Cross Apply SQL query listing each XML node seperately as a row in the return list of SELECT query.

Query SQL Server table XML data column with CROSS APPLY join

Please note that the starting point of the SELECT query is the main table "Books" which contains the XML data column. Then using a CROSS APPLY join we can reach XML data column "BooksXML" and its XML nodes "book" and alias it.

SELECT
 Books.Category,
 sqlXML.value('@category','varchar(50)') as SubCategory,
 sqlXML.value('.','varchar(50)') as [Book Title],
 Books.BooksXML
FROM Books
CROSS APPLY BooksXML.nodes('/root/book') as XMLData(sqlXML)

In the SELECT list, programmers will see the repeating table columns from Books table and unique XML attribute and XML node text value named as Book Title.

What is interesting in this SELECT statement is that the CROSS APPLY enables T-SQL programmers to apply this technique to different XML hierarch levels formed of different XML nodes on the same SQL Server XML SELECT statement. I'll try to show this example on an other SQL XML tutorial.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







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