Justin Cooney

There might come a day where you need to query a SQL Server table that has embedded XML rows. This is basically like having a table inside of a table. A simple example of where you might find this is in historical logging. So for example this might be a historical table where each day data is saved into a table row with a time stamp, and a bunch of historical information is saved as XML into the table.

A Pet Store Example

Let’s imagine that we have a pet store that has daily backups of its pets into a SQL Server historical table called PetsHist. That table has a timestamp as well as an XML datatype column that saves the pets at the store on that day.

Expected Output

In the example table above you can see we have two historical rows containing XML data in stored in the row attributes. For audit purposes we’d like a report of what types of cats were in the store on each day.

So basically, this is the output we want to see:

T-SQL Code

To output the table above from the SQL Server table and the embedded XML potentially containing many rows in the XML we can use a temporary table variable, a cursor, and a row-level XML query. Here is the code:

DECLARE @TableName TABLE ( 
Year INT,
Month INT,
Day INT,
Pet varchar(200),
Type varchar(200),
Name varchar(200)
)
DECLARE @XMLFile XML
declare @Year INT
declare @Month INT
declare @Day INT
DECLARE db_cursor CURSOR FOR 
SELECT  
      [Year]
      ,[Month]
      ,[Day]
      ,[Pets] 
FROM  PetsHist
where Year=2023
order by Day asc

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @Year,@Month,@Day,@XMLFile  

WHILE @@FETCH_STATUS = 0  
BEGIN  
INSERT INTO @TableName
SELECT @Year,@Month,@Day,
   ref.value('@Pet[1]', 'VARCHAR (100)') AS Pet
  ,ref.value('@Type[1]', 'VARCHAR (100)') AS Type
  ,ref.value('@Name[1]', 'VARCHAR (100)') AS Name
FROM @XMLFile.nodes('/root/row') xmlData( ref )
WHERE  ref.value('@Type[1]', 'VARCHAR (100)') = 'cat'

      FETCH NEXT FROM db_cursor INTO @Year,@Month,@Day,@XMLFile  
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

select * from @TableName

Summary

The code above will give us the results we need.

1. For starters we create a temporary table that we will populate as we get what we want from the existing table and split out the XML rows
2. Within the cursor we can query the XML field of that row just like a table now. We can look for one or more rows in the XML containing the data that we want. To loop through each row in the XML we use the syntax that defines what each row is from the root XML node: FROM @XMLFile.nodes('/root/row') xmlData( ref )
3. To get a specific attribute like ‘Pet‘ in the row we use the syntax: ref.value('@Pet[1]', 'VARCHAR (100)') AS Pet