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

)

Code

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

Code

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

Code

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)

Code

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)

Code

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.