How to select data into table from "select for xml raw"?

Question

SQL

select for xml raw selects data in the format attribute: value
How to select data back into the table if the names of the attributes are not known in advance?

Answer 1

As a result solved by means of dynamic SQL.

declare 
@colNames TABLE(
  id INT IDENTITY,
  colName nvarchar(max)
);
declare @XMLVal XML;
set @XMLVal = '<row link="someLink" DATASOURCE="SomeSystem" obj_id="1" err_id="1" />
<row link="someLink" DATASOURCE="SomeSystem" obj_id="1" err_id="2" />';
-----DEFINE COLUMN NAMES-------------
insert into @colNames
select distinct
       t.c.value('local-name(.)', 'nvarchar(128)') as [ID]
from @XMLVal.nodes('row/@*') as t(c) 
-----COUNT COLUMNS QNTY---------------------
DECLARE @MaxCount INT;
SELECT @MaxCount = count(*) from @colNames
-----GENERATE SQL---------------------------
DECLARE @SQL NVARCHAR(max), @i INT, @curentColumnName nvarchar(max);
SET @i = 0;
SET @SQL = 'declare @XMLVal XML;
SET @XMLVal = ''<row link="someLink" DATASOURCE="SomeSystem" obj_id="1" err_id="1" />
<row link="someLink" DATASOURCE="SomeSystem" obj_id="1" err_id="2" />'';
select ';
WHILE @i < @MaxCount
BEGIN
   SET @i = @i + 1;
     select @curentColumnName = colName from @colNames where id = @i;
     SET @SQL = @SQL + ' t.c.value(''@' + @curentColumnName + ''', ''nvarchar(128)'') as ' + @curentColumnName;
    if (@i < @MaxCount ) begin
       SET @SQL = @SQL + ', ';
    end 
 END
 SET @SQL = @SQL + ' from @XMLVal.nodes(''/row'') as t(c)'; 
 EXEC sp_executesql @SQL;