Try this:

DECLARE @input XML = '<dataset> 
 <metadata>
  <item name="NAME_LAST" type="xs:string" length="62" /> 
  <item name="NAME_FIRST" type="xs:string" length="62" /> 
  <item name="NAME_MIDDLE" type="xs:string" length="32" />
 </metadata>
<data>
<row>
 <value>SMITH</value> 
 <value>MARY</value> 
 <value>N</value> 
</row>
<row>
 <value>SMITH2</value> 
 <value>MARY2</value> 
 <value>N2</value> 
</row>
</data>
</dataset>'

INSERT INTO dbo.YourTable(ColName, ColFirstName, ColOther)
   SELECT
      Name = XCol.value('(value)[1]','varchar(25)'),
      FirstName = XCol.value('(value)[2]','varchar(25)'),
      OtherValue = XCol.value('(value)[3]','varchar(25)')
   FROM 
      @input.nodes('/dataset/data/row') AS XTbl(XCol)

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

Yes, there are issues when you try to insert XML into SQL Server 2008 and the XML contains an encoding instruction line.

I typically get around using the CONVERT function which allows me to instruct SQL Server to skip those instructions - use something like this:

INSERT INTO testfiles
  (filename, filemeta) 
VALUES 
  ('test.mp3', CONVERT(XML, N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>......', 2));

It has definitely helped me get various encoded XML stuff into SQL Server.

See the MSDN docs on CAST and CONVERT - a bit down the page there's a number of styles you can use for CONVERT with XML and some explanations about them.

Full query as you want:

DECLARE @XmlData XML
Set @XmlData = '<vehicles> 
  <vehicle>
    <vehiclereg>AB12CBE</vehiclereg>
    <anotherprop>BLAH</anotherprop>
  </vehicle>
  <vehicle>
    <vehiclereg>AB12CBE</vehiclereg>
    <anotherprop>BLAH</anotherprop>
  </vehicle>
</vehicles>'
SELECT T.Vehicle.value('./vehiclereg[1]', 'NVARCHAR(10)') AS vehiclereg,
       T.Vehicle.query('.'),
       GETDATE()
FROM @XmlData.nodes('/vehicles/vehicle') AS T(Vehicle)