Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @receiveTable TABLE(xml_data XML) DECLARE @xml_data XML
- DECLARE @strSQL NVARCHAR(2000)
- SET @strSQL = 'SELECT * INTO #tmp1 FROM sysobjects;
- DECLARE @tbl TABLE(xml_data xml);
- DECLARE @xml xml;
- Set @xml = (Select * from #tmp1 FOR XML AUTO);
- INSERT INTO @tbl(xml_data) SELECT @xml;
- SELECT * FROM @tbl'
- INSERT INTO @receiveTable EXEC (@strSQL)
- SET @xml_data = (SELECT * FROM @receiveTable)
- SELECT @xml_data
- 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')
Add Comment
Please, Sign In to add comment