Great post. Here's how to dinamically create query to convert columns from XML. I used this in ServiceBroker app


Code:
DECLARE @XmlDocument XML;
		SET @XmlDocument = (select TOP 1 * FROM BRO.Dialogs FOR XML AUTO, BINARY BASE64, ROOT('root'));
		--SELECT @XmlDocument	;
		
		declare @SQL varchar(max), @SQL2 varchar(max), @SQL3 varchar(max);
	    select @SQL = coalesce(@SQL + ','+ space(1),' ') + quotename(COLUMN_NAME),
			@SQL2 = coalesce(@SQL2 + ',' + space(1),' ') + (CASE WHEN DATA_TYPE = 'binary'
				   THEN  'dbo.f_Base64ToBinary(' + COLUMN_NAME + ')'
				   ELSE COLUMN_NAME END),
		    @SQL3 = coalesce(@SQL3 + ',' + space(1),' ') + (CASE WHEN DATA_TYPE = 'binary'
				   THEN  COLUMN_NAME + ' ' + 'VARCHAR(max)'
				   WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN COLUMN_NAME + ' ' + DATA_TYPE +'(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(20)) + ')'
				   ELSE COLUMN_NAME + ' ' + DATA_TYPE END)
		from INFORMATION_SCHEMA.COLUMNS
		where TABLE_SCHEMA = 'BRO' AND TABLE_NAME = 'Dialogs'
		and COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' +TABLE_NAME), COLUMN_NAME, 'IsIdentity') <> 1
		ORDER BY ORDINAL_POSITION;
		--SELECT @SQL, @SQL2, @SQL3;
		
		declare @var_query nvarchar(max)
		
		SET @var_query = '
		DECLARE @DocHandle int;
		DECLARE @XmlDocument XML;
		SET @XmlDocument = ''' +CAST(@XmlDocument AS NVARCHAR(max)) + ''';
		-- Create an internal representation of the XML document.
		EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;
		SELECT  '+ @SQL2+ ' 
		FROM OPENXML (@DocHandle, ''/root/BRO.Dialogs'',1)
		WITH ( '+ @SQL3 +' );'
		
		--SELECT @var_query
		
		exec dbo.sp_executesql @statement = @var_query