code_junkie

Parsing XML into a SQL table WITHOUT predefining structure. Possible?

Nov 13th, 2011
97
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.48 KB | None | 0 0
  1. DECLARE @receiveTable TABLE(xml_data XML) DECLARE @xml_data XML
  2. DECLARE @strSQL NVARCHAR(2000)
  3. SET @strSQL = 'SELECT * INTO #tmp1 FROM sysobjects;
  4. DECLARE @tbl TABLE(xml_data xml);
  5. DECLARE @xml xml;
  6. Set @xml = (Select * from #tmp1 FOR XML AUTO);
  7. INSERT INTO @tbl(xml_data) SELECT @xml;
  8. SELECT * FROM @tbl'
  9.  
  10. INSERT INTO @receiveTable EXEC (@strSQL)
  11. SET @xml_data = (SELECT * FROM @receiveTable)
  12. SELECT @xml_data
  13.  
  14. declare @tbl_xml xml
  15. set @tbl_xml = (
  16. select @xml_data.query('
  17. <table>
  18. {for $elem in /descendant::node()[local-name() != ""]
  19. return <row name="{local-name($elem)}">
  20. {for $attr in $elem/@*
  21. return <col name="{local-name($attr)}" value="{$attr}" />}
  22. </row>}
  23. </table>'
  24. )
  25. )
  26.  
  27. declare @sql_def_tbl varchar(max)
  28. select @sql_def_tbl =
  29. coalesce(@sql_def_tbl,'')
  30. +'declare @tbl table ('+substring(csv,1,len(csv)-1)+') '
  31. from (
  32. select (
  33. select ''+col.value('@name','varchar(max)')+' varchar(max),'
  34. from row.nodes('col') r(col) for xml path('')
  35. ) csv from @tbl_xml.nodes('//row[1]') n(row)
  36. ) x
  37.  
  38. declare @sql_ins_rows varchar(max)
  39. select @sql_ins_rows =
  40. coalesce(@sql_ins_rows,'')
  41. +'insert @tbl values ('+substring(colcsv,1,len(colcsv)-1)+') '
  42. from (
  43. select (
  44. select ''''+col.value('@value','varchar(max)')+''','
  45. from row.nodes('col') r(col) for xml path('')
  46. ) colcsv from @tbl_xml.nodes('//row') t(row)
  47. ) x
  48.  
  49. exec (@sql_def_tbl + @sql_ins_rows + 'select * from @tbl')
Add Comment
Please, Sign In to add comment