Coding truck

Querying xml columns with sql server

Today I learned about querying xml with sql using t-sql. This is the first time that I had run into this use case and found it interesting. 

The problem

There was a column in a table that contained a bunch of xml. I needed to get some of the information stored in the xml column. It was mixed with a bunch of other data that I didn't need and there were a bunch of rows.  Worse yet, it was a query that will run daily. 

I needed to find a fast and repeatable way to blend the data from the query with the data that I needed from the xml column. 

Reading xml by hand can be a pain, especially if you have a bunch of it to read. On the other hand, computers are great at reading xml. Luckily, I found out how to get MS SQL Server to read the data out of the xml and return it in a nice format. 

Xml data type methods to the rescue

A little searching and I found that t-sql has some really nice methods that will parse xml for you. 

  • The value() method - will give you the value stored in a given xml element
  • The nodes() method - will transform xml data into relational data

Using these two methods together will allow you to query xml data and use it like relational data

Parsing menu data

In the following example we will define a string with xml menu data in it. It contains 3 menu items. Each menu item has a name and a price. 

The sql will use the nodes() method to transform the data into rows.  The value() method will read names and prices of the menu items and make them into columns. 

declare @myXml xml

Set @myXml = 
'<Menu>
	<MenuItem>
		<Plate>Tacos Al Pastor</Plate>
		<Price>5.0</Price>
	</MenuItem>
	<MenuItem>
		<Plate>Asada Fries</Plate>
		<Price>6.99</Price>
	</MenuItem>
	<MenuItem>
		<Plate>Cheeseburger</Plate>
		<Price>9.49</Price>
	</MenuItem>
</Menu>'	

Select 
	menus.items.value('Plate[1]', 'varchar(20)') as Plate
	,menus.items.value('Price[1]', 'float') as Price
From
	@myXml.nodes('Menu/MenuItem') menus(items)
Plate Price
Tacos Al Pastor 5
Asada Fries 6.99
Cheeseburger 9.49

Joining xml data to query data

This discovery got me part of the way there.  But I still had a problem. I could read the xml data from the column but it was only one cell. I needed to find a way to repeat this for each row. 

Setting up the menu

It is time to do a little setup.  I'll make a temp table that contains two rows. Each row will contain a date, a location, and xml menu data. 

Create table #Menus (
	Date date
	,Location varchar(20)
	,Menu xml
)

Insert into #Menus (Date, Location, Menu) Values (
	'2019-01-01'
	,'San Antonio'
	,'<Menu>
		<MenuItem>
			<Plate>Tacos Al Pastor</Plate>
			<Price>5.0</Price>
		</MenuItem>
		<MenuItem>
			<Plate>Asada Fries</Plate>
			<Price>6.99</Price>
		</MenuItem>
		<MenuItem>
			<Plate>Cheeseburger</Plate>
			<Price>9.49</Price>
		</MenuItem>
	</Menu>'
)

Insert into #Menus (Date, Location, Menu) Values (
	'2019-01-02'
	,'Austin'
	,'<Menu>
		<MenuItem>
			<Plate>Pepperoni Pizza</Plate>
			<Price>8.99</Price>
		</MenuItem>
		<MenuItem>
			<Plate>Asada Fries</Plate>
			<Price>7.99</Price>
		</MenuItem>
	</Menu>'
)

I want to return a row that contains the date, location and the menu item data.

Querying the data returns both rows, but the xml data is still all jumbled together. I need join this query to the parsed xml data above

Select
	[Date]
	,Location
	,Menu
From
	#Menus M
Date Location Menu
1/1/2019 San Antonio <Menu><MenuItem><Plate>Tacos ... </Menu>
1/2/2019 Austin <Menu><MenuItem><Plate>Pepperoni ... </Menu>

Apply to the rescue

The apply operator does what I need.  Using apply, I can get exactly the results I am looking for

Select
	[Date]
	,Location
	,menus.items.value('Plate[1]', 'varchar(20)') as Plate
	,menus.items.value('Price[1]', 'float') as Price
From
	#Menus M outer apply
	M.Menu.nodes('/Menu/MenuItem') menus(items)
Date Location Plate Price
1/1/2019 San Antonio Tacos Al Pastor 5
1/1/2019 San Antonio Asada Fries 6.99
1/1/2019 San Antonio Cheeseburger 9.49
1/2/2019 Austin Pepperoni Pizza 8.99
1/2/2019 Austin Asada Fries 7.99

Try it for yourself

If you would like to play around with this, please view on sql fiddle

I like when I discover new things that sql can do. What have you discovered recently?

Next
Prev