how to solve error has fewer columns than were specified in the column list.

Question

here is my code to parse data but have error like this 'has fewer columns than were specified in the column list.; what I have to do?

WITH XMLNAMESPACES(
     'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey'
      AS XM)
SELECT SalesPersonID,
    StoreSurvey.AnnualSales.value('(XM:AnnualSales)','INT') as AnnualSales,
    StoreSurvey.AnnualRevenue.value('(XM:AnnualRevenue)','INT') as AnnualRevenue

    FROM Sales.Store
    CROSS APPLY Demographics.nodes('//XM:StoreSurvey') as
    StoreSurvey(AnnualSales,AnnualRevenue)
 

Answer 1

Hi,

has fewer columns than were specified in the column list

I am guessing that the issue is with this part, but until you provide the queries to preproduce the scenario, there is no much sense in discussion:

CROSS APPLY Demographics.nodes('//XM:StoreSurvey') as StoreSurvey(AnnualSales,AnnualRevenue)

Your Demographics.nodes probably returns only one column but you configured it to a SET of two columns StoreSurvey(AnnualSales,AnnualRevenue) and this make no sense.

---

Note! You use tables which we do not have, so we cannot reproduce your scenario.

You must provide queries to create the relevant table(s) and insert sample rows so we will be able to discuss and test

If the above is not clear and did not solve your issue then please provide (1) queries to create the relevant table(s) and insert some sample data. (2) What version of SQL Server you use. (3) according to the sample data, what is your expected result. (4) please describe hop you got your expected result out of the sample data.

By the way, You declare a CTE named XMLNAMESPACES and you never use it

More detailed information

SQL Server queries returns a tabular structure = set of rows that all the rows has the same structure. You cannot have different number of columns in rows in the same tabular structure. or insert more columns that the table has (insert 3 columns to table with 2 columns for example) and so on. This error usually raise when you have incompatibility in the number of rows in the set of columns that you use with the number of column in the entity which you want to insert these.

Here are some more demo of such type of issues, which can help you understand it:

use tempdb
GO


/*************************************************  */
CREATE TABLE [dbo].[Customer] (
    [CustomerID]      INT NOT NULL IDENTITY(1, 1),
    [CustomerName]    VARCHAR(100),
    [State]           CHAR(2)
)
GO

CREATE VIEW [dbo].[Customer_NY] (
    [CustomerID], [CustomerName], [State]
)
AS 
SELECT [CustomerID], [CustomerName]
FROM [dbo].[Customer]
GO
-- Issue: 'Customer_NY' has fewer columns than were specified in the column list.
--> A: You cannot configure the view with 3 column but only use 2 columns in the select

/*************************************************  */

CREATE TABLE T (id int)
GO
INSERT T(id) VALUES (1,'s')
GO
-- There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

/*************************************************  */

Answer 2

As Ronen says, this is the error:

 CROSS APPLY Demographics.nodes('//XM:StoreSurvey') as
 StoreSurvey(AnnualSales,AnnualRevenue)

The nodes function is a table-valued function, and it returns a single column of the xml data type. Each value in that column represents a single node in the XML document. It is a little bit special, because you cannot access it as such, you can only use data type methods on it to extract portions of it.

Here is a simple example that demonstrates how to shred an XML document:

DECLARE @x xml
SELECT @x =
  N'<Orders>
      <Order OrderID="13000" CustomerID="ALFKI"
             OrderDate="2006-09-20Z" EmployeeID="2">
         <OrderDetails ProductID="76" Price="123" Qty = "10"/>
         <OrderDetails ProductID="16" Price="3.23" Qty = "20"/>
      </Order>
      <Order OrderID="13001" CustomerID="VINET"
             OrderDate="2006-09-20Z" EmployeeID="1">
         <OrderDetails ProductID="12" Price="12.23" Qty = "1"/>
      </Order>
    </Orders>'
SELECT OrderID    = T.Item.value('@OrderID[1]',    'int'),
       CustomerID = T.Item.value('@CustomerID[1]', 'nchar(5)'),
       OrderDate  = T.Item.value('@OrderDate[1]',  'datetime'),
       EmployeeId = T.Item.value('@EmployeeID[1]', 'smallint')
FROM   @x.nodes('/Orders/Order') AS T(Item)

SELECT OrderID    = A.Item.value('@OrderID[1]',    'int'),
       ProductID  = B.Item.value('@ProductID[1]',  'smallint'),
       Price      = B.Item.value('@Price[1]',      'decimal(10,2)'),
       Qty        = B.Item.value('@Qty[1]',        'int')
FROM   @x.nodes('/Orders/Order') AS A(Item)
CROSS  APPLY A.Item.nodes('OrderDetails') AS B (Item)