T-SQL and XML

I was wary of XML when I first heard about it - mostly because I was still trying to wrap my head around other stuff that I was learning.  Therefore, I didnt learn much about it at first.  Then, I needed to pass multiple values to a stored procedure and looked into it.  I quickly fell in love!!  There are many posts about using XML within T-SQL.  I am posting one so that I can search on my own blog for this when I need it - I dont seem to keep it in my head long enough to use it the next time I need it. :)

I am going to use examples from my alerter system, that is where I am using it as I am writing this article - so 2 birds with 1 stone - cant beat that!  By the way, I will format the SQL code better - it looks weird being all gray!

Here is the T-SQL to set up the examples below:

-- I used a string of text for 2 reasons: 
-- 1) test and make sure the xml string looks right 
-- 2) xml can be read from a string variable or an actual xml variable 
declare @li_rv int
,@lv_lineBreak varchar(2) 
,@lv_recipientNames varchar(max) 
,@lx_names xml 
set @lv_lineBreak = char(13) + char(10) 
set @lv_recipientNames = '<Recipients>' + @lv_lineBreak
set @lv_recipientNames = @lv_recipientNames + '<Alert Name="ETL_nomatchfound">' + @lv_lineBreak
set @lv_recipientNames = @lv_recipientNames + '<Person FirstName="Glenda" LastName="Gable" EmailAddress="This email address is being protected from spambots. You need JavaScript enabled to view it." />' + @lv_lineBreak
set @lv_recipientNames = @lv_recipientNames + '<Person FirstName="Kermit" LastName="Frog" EmailAddress="This email address is being protected from spambots. You need JavaScript enabled to view it." />' + @lv_lineBreak
set @lv_recipientNames = @lv_recipientNames + '<Alert>' + @lv_lineBreak
set @lv_recipientNames = @lv_recipientNames + '<Alert Name="job_step1completed">' + @lv_lineBreak
set @lv_recipientNames = @lv_recipientNames + '<Person FirstName="Glenda" LastName="Gable" EmailAddress="This email address is being protected from spambots. You need JavaScript enabled to view it." />' + @lv_lineBreak
set @lv_recipientNames = @lv_recipientNames + '<Person FirstName="Big" LastName="Bird" EmailAddress="This email address is being protected from spambots. You need JavaScript enabled to view it." />' + @lv_lineBreak
set @lv_recipientNames = @lv_recipientNames + '<Person FirstName="Grover" LastName="Monster" EmailAddress="This email address is being protected from spambots. You need JavaScript enabled to view it." />' + @lv_lineBreak
set @lv_recipientNames = @lv_recipientNames + '</Alert>' + @lv_lineBreak
set @lv_recipientNames = @lv_recipientNames + '</Recipients>'

Now that we have that out of the way.... there are multiple ways of reading that XML. I am going to show examples of using Open XML (for XML formatted string variables) and XQuery (for xml variables).

You can also use the BULK import/export for XML documents. Here is a link to describe that - I dont have a reason to use it yet, so I will let the experts explain it better :) -- http://msdn.microsoft.com/en-us/library/ms191184.aspx

OpenXML

-- ==================
-- This uses OpenXML to read XML formatted string data, parse it as XML and get the values from within the XML string

-- create an internal representation of the XML document (using the string formatted with XML tags)
exec sp_xml_preparedocument @li_rv OUTPUT, @lv_recipientNames

-- bring back results from a specific node level
select Name
FROM       openxml (@li_rv, '/Recipients/Alert',1)
            with (Name  varchar(25))

select FirstName, LastName, EmailAddress
from       openxml (@li_rv, '/Recipients/Alert/Person',1)
            with (FirstName  varchar(50)
					,LastName varchar(50)
					,EmailAddress varchar(75))

-- bring back results from multiple node levels
select AlertName, RecipFirstName, RecipLastName, EmailAddress
from       openxml (@li_rv, '/Recipients/Alert/Person',2)
            with (AlertName  varchar(25)         '../@Name'
					,RecipFirstName  varchar(50) '@FirstName'
					,RecipLastName varchar(50)        '@LastName'
					,EmailAddress varchar(75)    '@EmailAddress')

-- remove the document (free up the memory); "A parsed document is stored in the internal cache of SQL Server." (http://msdn.microsoft.com/en-us/library/ms190353.aspx)
exec sp_xml_removedocument @li_rv

-- For more information check out the Microsoft site:
-- http://msdn.microsoft.com/en-us/library/ms186918.aspx

XQuery

-- ==================
-- This uses XQuery to get the values from within the XML string
-- Note: the red squiggly lines appear under item.value in the select statement - that is ok, when it is run, it works
select AlertName = item.value('(../@Name)','varchar(25)')
,FirstName = item.value('(@FirstName)','varchar(50)')
,LastName = item.value('(@LastName)','varchar(50)')
,EmailAddress = item.value('(@EmailAddress)','varchar(75)')
from @lx_names.nodes('//Recipients/Alert/Person') as Recipients(item)
--where item.value('(../@Name)','varchar(25)') = 'ETL_nomatchfound'