FOR XML and back again

I’ve used the 2005 XML features for a few things now and I’m getting to quite like it. One thing I’ve found is that it is a lot easier to get data into XML than it is to get it back out again.

The following is a stored procedure that given an XML variable and a table name will dynamically construct and execute the SQL needed to shred XML back into a table. I knocked it together quite quickly and at the moment it only works on XML that has been created by FOR XML AUTO, although it could quite easily be modified for the other XML options.

CREATE PROC dbo.uSpShredTable @Xml XML, @TableName SYSNAME AS

       DECLARE @Sql NVARCHAR(MAX)

       SELECT @Sql = 'SELECT ' +

           STUFF((SELECT '      ,T.Data.value(''@' +

                         c.name + ''', ''' +

                         t.name +

                         CASE WHEN c.user_type_id IN (165,167,173,175,231,239)

                              THEN '(' + CONVERT(VARCHAR, c.max_length) + ')'

                              WHEN c.user_type_id IN (106, 108)

                              THEN '(' + CONVERT(VARCHAR, c.precision)

                                   + ', ' + CONVERT(VARCHAR, c.scale) + ')'

                              ELSE '' END +

                         ''') AS ' + c.name + CHAR(10)

                  FROM   sys.columns c

                         INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

                  WHERE  object_id = OBJECT_ID(@TableName)

                         AND t.name !='xml'

                  FOR XML PATH('')), 1, 7, '') +

           'FROM   @Xml.nodes(''/' + @TableName + ''') T(Data)'

    EXEC sp_executesql @Sql, N'@Xml XML', @Xml = @Xml

GO

Example usage:

-- Get some XML

DECLARE @Xml XML

SET @Xml = (SELECT * FROM Person.Contact FOR XML AUTO)

-- Get a table back

EXEC uSpShredTable @Xml, 'Person.Contact'

GO

How it works

The stored procedure constructs and executes the following SQL:

SELECT T.Data.value('@ContactID', 'int') AS ContactID

      ,T.Data.value('@NameStyle', 'NameStyle') AS NameStyle

      ,T.Data.value('@Title', 'nvarchar(16)') AS Title

      ,T.Data.value('@FirstName', 'Name') AS FirstName

      ,T.Data.value('@MiddleName', 'Name') AS MiddleName

      ,T.Data.value('@LastName', 'Name') AS LastName

      ,T.Data.value('@Suffix', 'nvarchar(20)') AS Suffix

      ,T.Data.value('@EmailAddress', 'nvarchar(100)') AS EmailAddress

      ,T.Data.value('@EmailPromotion', 'int') AS EmailPromotion

      ,T.Data.value('@Phone', 'Phone') AS Phone

      ,T.Data.value('@PasswordHash', 'varchar(128)') AS PasswordHash

      ,T.Data.value('@PasswordSalt', 'varchar(10)') AS PasswordSalt

      ,T.Data.value('@rowguid', 'uniqueidentifier') AS rowguid

      ,T.Data.value('@ModifiedDate', 'datetime') AS ModifiedDate

FROM   @Xml.nodes('/Person.Contact') T(Data)