EDIT:  Ok, so as is pointed out in the comments below SQL Server comes with a very powerful XML Parser already OPENXML and sp_xml_preparedocument.  I posted a code snipet in the comments I got from BOL so you can see how it works.  While my routine operaes in a different manner, it seems to have been an exercise in futility...but it was fun writing it...annd hey maybe it will come in hany.  Who knows.

A poster over at SQLTeam asked a question on how to “read“ xml data from a string.  Now I had always toyed with the idea of parsing XML data so it could be loaded in to a normalized database.  We had already implemented here in prouction, the delivery of data to a Java front in XML format (for which the developers were very happy), but we never did the reverse.  Now this code below assumes that the xml data will not be malformed (i.e. there is no checking) and that the “elements“ of the xml are 1 per tag.  They all do not have to be there, so nulls would be inserted, it can have more or different tags, but they will get ignored, and if more than 1 “element“ tag type exists, the last one wins.  To modify this for more or different tags, you will only have to modify the IF/ELSE IF code block, and the dynamic sql component.  Happy Coding.

USE Northwind
GO

CREATE TABLE myTable99(fname char(20), lname char(30), phone char(20))
GO

DECLARE @xml varchar(8000), @data varchar(8000), @tag varchar(255), @sql varchar(8000)
, @fname char(20), @lname char(30), @phone char(20)
SET @xml = '<fname>Brett</fname><lname>Kaiser</lname><phone>555-1234<phone>'
SELECT @tag = '', @data = ''

WHILE LEN(@xml) <> LEN(@tag+@data+@tag)+3
  BEGIN
 SELECT @tag=SUBSTRING(@xml,CHARINDEX('<',@xml), CHARINDEX('>',@xml))
 SELECT @data = SUBSTRING(@xml,LEN(@tag)+1,CHARINDEX('<',@xml,LEN(@tag))-LEN(@tag)-1)
 
      IF @tag = 'fname'
  SELECT @fname = @data
 ELSE IF @tag = 'lanme'
  SELECT @lname = @data
 ELSE IF @tag = 'phone'
  SELECT @phone = @data
 
 SELECT @xml = SUBSTRING(@xml,LEN(@tag+@data+@tag)+2,LEN(@xml)-LEN(@tag+@data+@tag)+2)
 
 SELECT @xml,@fname,@data,@tag,LEN(@xml),LEN(@tag+@data+@tag)+2
  END

SELECT @tag=SUBSTRING(@xml,CHARINDEX('<',@xml), CHARINDEX('>',@xml))
SELECT @data = SUBSTRING(@xml,LEN(@tag)+1,CHARINDEX('<',@xml,LEN(@tag))-LEN(@tag)-1)

     IF @tag = ''
 SELECT @fname = @data
ELSE IF @tag = ''
 SELECT @lname = @data
ELSE IF @tag = ''
 SELECT @phone = @data

SELECT @sql = 'INSERT INTO myTable99(fname,lname,phone) '
  + 'SELECT ''' + RTRIM(@fname) + ''''
  + ',' + ''''  + RTRIM(@lname) + ''''
  + ',' + ''''  + RTRIM(@phone) + ''''
SELECT @sql

EXEC(@sql)

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO