So going back to
Can it be done? Yes… it’s not pretty but yes it can be done. I emailed this to Brent Ozar to see if he was an XML guru as we had gone back on forth on Twitter about whether or not it’s possible. He pointed me to StackOverflow and 1 day later a user by the name FreddyB solved the problem.
DECLARE @tbl_xml XML
SET @tbl_xml = (
SELECT @xml_data.query('
<table>
{for $elem in /descendant::node()[local-name() != ""]
return <row name="{local-name($elem)}">
{for $attr in $elem/@*
return <col name="{local-name($attr)}" value="{$attr}" />}
</row>}
</table>'
)
)
DECLARE @sql_def_tbl VARCHAR(MAX)
SELECT @sql_def_tbl =
COALESCE(@sql_def_tbl,'')
+'declare @tbl table ('+SUBSTRING(csv,1,LEN(csv)-1)+') '
FROM (
SELECT (
SELECT ''+col.value('@name','varchar(max)')+' varchar(max),'
FROM row.nodes('col') r(col) FOR XML path('')
) csv FROM @tbl_xml.nodes('//row[1]') n(row)
) x
DECLARE @sql_ins_rows VARCHAR(MAX)
SELECT @sql_ins_rows =
COALESCE(@sql_ins_rows,'')
+'insert @tbl values ('+SUBSTRING(colcsv,1,LEN(colcsv)-1)+') '
FROM (
SELECT (
SELECT ''''+col.value('@value','varchar(max)')+''','
FROM row.nodes('col') r(col) FOR XML path('')
) colcsv FROM @tbl_xml.nodes('//row') t(row)
) x
EXEC (@sql_def_tbl + @sql_ins_rows + 'select * from @tbl')
Now I’m sure that now the base structure is there and works there is probably some optimizations that can be made for performance… but there ya go… it CAN be done.
