Wednesday, March 23, 2011

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

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