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

Question

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

FreddyBowen · Accepted Answer · 2009-01-15 19:00:05Z

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')

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related