Using XML parameters in SQL Server 2000 and above

The following is a minimal sample of extracting attribute based an element based data from an xml document in SQL Server.

declare @text varchar(1000)
set @text = ‘<a firstname=”Joe” surname=”Blogs” >42</a>’
DECLARE @idoc int
select @text

declare @name varchar(10)
declare @name2 varchar(10)

— Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @text
— Execute a SELECT statement using OPENXML rowset provider.
SELECT @name=surname, @name2 = [firstname]
FROM OPENXML (@idoc, ‘/a’,1)
      WITH ([firstname]  varchar(10),
            surname varchar(20))

select @name, @name2

SELECT age=[text]
FROM OPENXML (@idoc, ‘/a/text()’,2)

EXEC sp_xml_removedocument @idoc