Sql-server – Insert XML File into specific sql table

sql serverxml

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.

Best Answer

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

Related Solutions

Sql-server – I would like to better understand the SQL Server security model in terms of users and groups to possibly replace an XML heirarchy model I am using

What you are trying to do is basic row level filtering. Normally this wouldn't be done using SQL Users and Roles but instead with application level users within your application. Basically you have your users table (and/or a groups table) which are worked with within the application.

Then you create a table between the users and the rows which you need to provide the permissions and do joins between then so that users can only view rows which they should.

There are several books and articles on the subject.

Sql-server – List of all missing order numbers that are not in the table

First create an auxiliary numbers table larger than the maximum range you will ever be interested in.

CREATE TABLE dbo.Numbers
(
N INT primary key
);   


WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
    E02(N) AS (SELECT 1 FROM E00 a, E00 b),
    E04(N) AS (SELECT 1 FROM E02 a, E02 b),
    E08(N) AS (SELECT 1 FROM E04 a, E04 b),
    E16(N) AS (SELECT 1 FROM E08 a, E08 b),
    E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
INSERT 
INTO  dbo.Numbers
SELECT TOP(100000) N
FROM cteTally
OPTION (MAXDOP 1);

Then the query is simple

DECLARE @Start INT = 9001
        , @End INT = 9009

SELECT @Start + N - 1 AS ORDERNUMBER
FROM   dbo.Numbers
WHERE  N <= ( 1 + @End - @Start )
EXCEPT
SELECT [ORDERNUMBER]
FROM   [dwdata].[dbo].[ORDERS]
ORDER BY [ORDERNUMBER] DESC

Best Answer

Related Solutions

Sql-server – I would like to better understand the SQL Server security model in terms of users and groups to possibly replace an XML heirarchy model I am using

Sql-server – List of all missing order numbers that are not in the table

Related Question