Insert XML File into specific sql table

Question

I have the following xml document

<XML_Articles>
 <Articles>
 <ID>12</ID>
 <ReferenceNum>123</ReferenceNum>
 <ArName>الهدف</ArName>
 <EnName>GOAL</EnName>
 <PublicationDate>2016年11月15日</PublicationDate>
 <TypeID>15</TypeID>
 <CreatedBy>2</CreatedBy>
 <CreatedOn>2016年11月15日</CreatedOn>
 </Articles>
</XML_Articles>

insert them into table by

INSERT INTO dbo.Articles
(ReferenceNum,ArName,EnName,PublicationDate,PublisherID,TypeID,CreatedBy,CreatedOn)
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.

• Mark Sinkinson: A SINGLE_BLOB is exactly how it sounds. It's a 'single-row, single-column rowset of type varbinary(max).' Read the documentation on OPENROWSET msdn.microsoft.com/en-us/library/ms190312.aspx

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
 FROM OPENROWSET(BULK 'C:\Test\TestXml.txt', SINGLE_CLOB) AS x
 )
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)
GO
select * from #table