Query XML in SQL Server for Different Hierarchy Levels using Cross Apply
In this SQL Server XML tutorial, SQL developers will query XML data for XML nodes and attributes with different hierarchy levels. If XML data in your SQL query contain complex data structures like top level items and XML nodes under these top level items, T-SQL programmers can still query XML data using Cross Apply SQL join and get the desired set of data rows out of the XML data.
Complex XML Data Structure to Query in SQL Server 2012
Let's start our SQL Server XML tutorial by forming a sample XML data which store a country list and beneath each country it contains a list of cities in that country. This example XML fragment contains a hierarchical form of XML nodes country and city for your SQL XML tutorial.
declare @sqlxml xml
set @sqlxml = N'
<root>
<country>
<name>Turkey</name>
<city cityname="Ankara">
<trafficcode>6</trafficcode>
</city>
<city cityname="Balıkesir">
<trafficcode>10</trafficcode>
</city>
<city cityname="Bingöl">
<trafficcode>12</trafficcode>
</city>
<city cityname="Eskişehir">
<trafficcode>26</trafficcode>
</city>
<city cityname="İstanbul">
<trafficcode>34</trafficcode>
</city>
<city cityname="Uşak">
<trafficcode>64</trafficcode>
</city>
</country>
<country>
<name>Germany</name>
<city cityname="Munich">
<trafficcode></trafficcode>
</city>
</country>
</root>
'
Sample XML code contains two top level country data and 6 cities under first country and only one city for the second country. And I want to query XML data to have result list of 7 rows with county and city details after applying SQL XML query methods on SQL Server 2012.
SQL XML Query for Top Level Hierarchy Nodes in SQL Server
As our sample XML data is ready, as SQL XML programmers we can now build T-SQL Select query to retrieve the city names, city details and the country where they are. Let's start with the country name which is on the top level hierarchy that we want in the Select list.
Below SQL XML query selects values from a table formed by the nodes of country in XML type SQL variable. Selected values are the text values of the first Name XML node under each country node.
select country.value('name[1]','nvarchar(50)') as country
from @sqlxml.nodes('/root/country') as XMLtable1(country)
Here is the output of the above SQL XML query when I run it on my SQL Server 2012 database instance.
SQL Server XML query example listing top XML nodes
Query XML Data for All Hierarchy Levels using Cross Apply Join
Let's update our XML query to add city and city details beside country data when we execute it on SQL Server database.
What is important in this query is the CROSS APPLY part where we join country data with its subnodes city.
Please note that there is a hierarchy between Country and City XML data.
Since we want to list country name with each of its cities, in the CROSS APPLY part we have to use the XML fragment under the Country node.
select
country.value('name[1]','nvarchar(50)') as countryname,
city.value('@cityname','nvarchar(50)') as city,
city.value('trafficcode[1]','smallint') as trafficcode
from @sqlxml.nodes('/root/country') as XMLtable1(country)
cross apply XMLtable1.country.nodes('city') as XMLtable2(city)
SQL programmers will realize that we have used the alias "XMLtable1(country)" for the country nodes table.
The city XML nodes under each country node can be fetched by querying for the city nodes of this alias table XMLtable1(country).
So SQL Server developers can use SQL XML syntax XMLtable1.country.nodes('city') for returning the cities nodes of each country as a table.
Here is the output of the above SQL Server XML query when I execute it on SQL Server 2012.
Query complex XML structures in SQL Server using Cross Apply
Query Fails with Hierarchy Levels if Cross Apply Join is False
If Transact-SQL programmers fail to pay attention to the join between Country and Cities which are under the Country node in the XML hierarchy, the output result set of the SQL XML query will be a CROSS JOIN instead of a CROSS APPLY. This error will cause all countries to be matched with all cities instead of hierarchical matching. And SQL XML query will fail to return the desired set of rows. If XML data is huge and SQL developer or SQL Server database administrator does not have the chance to test its SQL XML code, this wrong Transact-SQL statement will not be easily determined and will cause a lot of false results in your database application.
Let's see the code in action that produces false results.
select
country.value('name[1]','nvarchar(50)') as country,
city.value('@cityname','nvarchar(50)') as city,
city.value('trafficcode[1]','smallint') as trafficcode
from @sqlxml.nodes('/root/country') as XMLtable1(country)
cross apply @sqlxml.nodes('/root/country/city') as XMLtable2(city)
And this is the SQL result set with mismatched country and cities. This type of a SQL Cross Apply join is not the correct method to query XML data for nodes and attributes in different hierarchy levels
Take care on your Cross Apply join structures while querying XML node and attributes of different hierarchy levels.
Query XML in SQL Server Table
If XML data is stored in SQL Server database table column instead of XML data type SQL variable, then SQL programmers should change their T-SQL codes to query first main database table first. And then the CROSS APPLY SQL joins will be added to add the XML nodes as imaginary tables within the SQL Select query.
-- This is sample SQL Server table to store XML data
CREATE TABLE SQLXML (
id int identity(1,1),
description varchar(100),
SQLXMLData XML
)
-- Insert sample XML data in SQL Server table
INSERT INTO SQLXML
SELECT 'Countries and Cities',
N'
<root>
<country>
<name>Turkey</name>
<city cityname="Ankara">
<trafficcode>6</trafficcode>
</city>
<city cityname="Balıkesir">
<trafficcode>10</trafficcode>
</city>
<city cityname="Bingöl">
<trafficcode>12</trafficcode>
</city>
<city cityname="Eskişehir">
<trafficcode>26</trafficcode>
</city>
<city cityname="İstanbul">
<trafficcode>34</trafficcode>
</city>
<city cityname="Uşak">
<trafficcode>64</trafficcode>
</city>
</country>
<country>
<name>Germany</name>
<city cityname="Munich">
<trafficcode></trafficcode>
</city>
</country>
</root>
'
-- Insert sample XML data in SQL Server table
INSERT INTO SQLXML
SELECT 'Countries and Cities',
N'
<root>
<country>
<name>Russia</name>
<city cityname="Moscow">
<trafficcode></trafficcode>
</city>
</country>
<country>
<name>England</name>
<city cityname="London">
<trafficcode></trafficcode>
</city>
</country>
</root>
'
-- SQL XML query to list country and city nodes and attributes
SELECT
country.value('name[1]','nvarchar(50)') as countryname,
city.value('@cityname','nvarchar(50)') as city,
city.value('trafficcode[1]','smallint') as trafficcode
FROM SQLXML
CROSS APPLY SQLXMLData.nodes('/root/country') as XMLtable1(country)
CROSS APPLY XMLtable1.country.nodes('city') as XMLtable2(city)
I believe to query SQL database table is clear. But you can check an other SQL XML tutorial Query SQL Server Database Table XML data using CROSS APPLY for more examples and explanation.
I hope this SQL Server XML tutorial became useful for SQL programmers and database administrators to deail with XML in SQL and to query XML data using SQL Server cross apply join.