Inserting a XML table into a SQL table

Question

I'm trying to put a XML file into a SQL table. I've successfully gotten the XML file into the SQL server using the following code:

declare @xmldata as xml

set @xmldata = (SELECT CONVERT(XML, BulkColumn)AS Bulkcolumn
FROM OPENROWSET (BULK 'filedestination', SINGLE_BLOB) as X)

and the following snippet of code is where I get an error. I'm aware that I must use an INSERT INTO statement but I'm not sure where I'm going wrong here.

INSERT INTO t1(c1, c2, c3)
SELECT  
c1 AS 'c1',
c2 AS 'c2',
c3 as 'c3'

I'm currently using SQL Server 2005.

The XML file looks like this

<row>
<c1> DATA </c1>
<c2> DATA </c2>
<c3> DATA </c3>
</row>
...

Answer 1

With your last comment you did not address any user directly (e.g. with @Shnugo), so there was no alert. Nobody found your edit obviously...

Don't know if this is still an open issue, but this was an answer:

This is the way you are loading your file

declare @xmldata as xml;
set @xmldata = 
(
    SELECT CONVERT(XML, BulkColumn)AS Bulkcolumn
    FROM OPENROWSET (BULK 'filedestination', SINGLE_BLOB) as X
);

Now the variable @xmldata should contain XML like you've posted it in your edit. Just for testing I set the variable to some fitting content:

SET @xmldata=
'<root>
    <row>
        <c1>DATA1</c1>
        <c2>DATA2</c2>
        <c3>DATA3</c3>
    </row>
    <row>
        <c1>DATA4</c1>
        <c2>DATA5</c2>
        <c3>DATA6</c3>
    </row>
</root>';

Now I create a temp table to test the insert

CREATE TABLE #t1(c1 VARCHAR(100),c2 VARCHAR(100),c3 VARCHAR(100));

And this is the way to insert the data:

INSERT INTO #t1 
SELECT One.Rw.value('c1[1]','varchar(100)')
      ,One.Rw.value('c2[1]','varchar(100)')
      ,One.Rw.value('c3[1]','varchar(100)')
FROM @xmldata.nodes('/root/row') AS One(Rw);

SELECT * FROM #t1;

GO
DROP TABLE #t1;

This was the result:

c1      c2      c3
DATA1   DATA2   DATA3
DATA4   DATA5   DATA6