DECLARE @COLUMN_DETAIL_XML AS VARCHAR(MAX)
DECLARE @LIST_GUID AS VARCHAR(50)
DECLARE @IDOC INT
DECLARE @SQL VARCHAR(MAX)
SELECT
@COLUMN_DETAIL_XML = TP_FIELDS,
@LIST_GUID = TP_ID
FROM
LISTS LISTS
INNER JOIN
WEBS WEBS
ON
LISTS.TP_WEBID = WEBS.ID
WHERE
LISTS.TP_TITLE = 'Announcements'
AND
WEBS.FULLURL = '' -- root web site
-- add root node for "proper xml" and remove initial version stamp
SET @COLUMN_DETAIL_XML = '<root>' + SUBSTRING(@COLUMN_DETAIL_XML , CHARINDEX('<', @COLUMN_DETAIL_XML ), 99000) + '</root>
EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT, @COLUMN_DETAIL_XML
SELECT
*
INTO
#COLUMNS
FROM
(
SELECT
COLNAME,
NAME
FROM
OPENXML ( @idoc, '/root/Field', 3)
WITH (
AllowHyperlink varchar(1000),
AppendOnly varchar(1000),
ColName varchar(1000),
Description varchar(1000),
DisplaceOnUpgrade varchar(1000),
DisplayName varchar(1000),
Format varchar(1000),
FromBaseType varchar(1000),
[Group] varchar(1000),
ID varchar(1000),
IsolateStyles varchar(1000),
List varchar(1000),
Mult varchar(1000),
Name varchar(1000),
NumLines varchar(1000),
Percentage varchar(1000),
PrependId varchar(1000),
ReadOnly varchar(1000),
Required varchar(1000),
RichText varchar(1000),
RichTextMode varchar(1000),
RowOrdinal varchar(1000),
Sealed varchar(1000),
ShowField varchar(1000),
Sortable varchar(1000),
SourceID varchar(1000),
StaticName varchar(1000),
Type varchar(1000),
UnlimitedLengthInDocumentLibrary varchar(1000),
UserSelectionMode varchar(1000),
UserSelectionScope varchar(1000),
Version varchar(1000),
WorkflowStatusURL varchar(1000)
)
UNION
SELECT
COLNAME,
NAME
FROM
OPENXML ( @idoc, '/root/FieldRef', 3)
WITH (
ColName varchar(1000),
Name varchar(1000),
SourceID varchar(1000),
StaticName varchar(1000)
)
) G
EXEC SP_XML_REMOVEDOCUMENT @IDOC
--SELECT * FROM #COLUMNS
SET @SQL = 'SELECT
USERDATA.TP_ID AS ID,'
SELECT @SQL = @SQL + ' [USERDATA].[' + COLNAME + '] AS [' + NAME + '],
'
FROM
#COLUMNS G
WHERE
COLNAME IS NOT NULL AND NAME IS NOT NULL
SET @SQL = LEFT(@SQL, LEN(@SQL) - 3) + '
FROM
USERDATA USERDATA
INNER JOIN
LISTS LISTS
ON
USERDATA.TP_LISTID = LISTS.TP_ID
INNER JOIN
WEBS WEBS
ON
LISTS.TP_WEBID = WEBS.ID
WHERE
LISTS.TP_ID = ''' + @LIST_GUID + ''''
EXEC (@SQL)
DROP TABLE #COLUMNS