So using the code below... can I parse @xml_data
into a table structure without predefining the structure?
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
From stackoverflow
-
As in your
@xml_data
, if/element[1]
has the same number of attributes as/element[n]
and they're in the same order ltr, you can.It's not pretty, but you can:
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')
0 comments:
Post a Comment