I have the following xml document


insert them into table by

INSERT INTO dbo.Articles
SELECT * FROM OPENROWSET(BULK 'C:\Users\dana_\Desktop\DataBase\Assignments\Assignement 6\Articles.xml',SINGLE_BLOB) AS x

But I get an error:

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.


1 Answer 1


This error is fairly self-explanatory. The number of columns in the 'insert' column list do not match the number of columns in the 'select' column list. Check the number of columns being returned by your SELECT * I would suggest that it is rarely a good idea to use SELECT * when trying to insert into another table. You should always explicitly specify the selected columns.

I played around with your example XML data by putting it in a file and then executed the following

IF OBJECT_ID('tempdb..#Table') IS NOT NULL DROP TABLE #Table
Create table #Table (ID int, ReferenceNum varchar(20), ArName Varchar(20), EnName Varchar(20),
PublicationDate Date, CreatedBy Varchar(200), CreatedOn Varchar(20))
WITH XmlData
AS (
    SELECT cast(BulkColumn AS XML) AS XmlData
insert into #table
SELECT a.value('ID[1]', 'varchar(15)') AS ID
    ,a.value('ReferenceNum[1]', 'varchar(20)') AS ReferenceNum
    ,a.value('ArName[1]', 'varchar(20)') AS ArName
    ,a.value('EnName[1]', 'varchar(20)') AS EnName
    ,a.value('PublicationDate[1]', 'varchar(20)') AS PublicationDate
    ,a.value('CreatedBy[1]', 'varchar(20)') AS CreatedBy
    ,a.value('CreatedOn[1]', 'varchar(20)') AS CreatedOn
FROM xmlData
CROSS APPLY XmlData.nodes('XML_Articles/Articles') AS XMLArticles(a)
select * from #table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.