Invalid at the top level of the document

Question

0.00/5 (No votes)

See more:

Hi, I have a stored procedure in which i will select some details from the db. When i execute the stored procedure directly using SQL Studio 2016, i get the error as shown on title. While i have googled for many solutions online and tried them, none can successfully solve my problem. My stored procedure is as below:

SQL

DECLARE  
    -- Add the parameters for the stored procedure here  
    @p_CarId NVARCHAR(MAX) = '12345'
--AS  
BEGIN  
-- SET NOCOUNT ON added to prevent extra result sets from  
-- interfering with SELECT statements.  
SET NOCOUNT ON;
    DECLARE @xmlContent AS NVARCHAR(MAX)
    SET @xmlContent = '
    <ROOT>
	   <Vehicles CarId="'+ @p_CarId +'">
	   </Vehicles>
    </ROOT>
    ';

    DECLARE 
	   @hdoc AS INT;  
    EXEC sp_xml_preparedocument @hdoc OUTPUT , @xmlContent  
  
    DECLARE @Car TABLE 
    (  
	   CarId BIGINT
    )   
      
   
    INSERT INTO 
	   @Car  
    SELECT   
	   *  
    FROM   
	   OPENXML(@hdoc,'/ROOT/Vehicles/CarId',1) WITH(CarId BIGINT)  
  
    SELECT   
	     c.CarId  
	    ,cd.CarModel
	    ,cd.CarManufacturedDate
    FROM  
	   @Car AS c
	   INNER JOIN CarDetails AS cd
		  ON c.CarId = cd.CarId 
END

When i execute the above script, i keep on getting the error as shown in title. Any suggestions on this? Thank you.

What I have tried:

1. Search online for possible explanation and solution but to no avail.

Posted 18-Mar-18 20:10pm

Jamie888

Updated 19-Mar-18 19:51pm

v2

Add a Solution

Comments

Santosh kumar Pithani 20-Mar-18 2:11am

let me know why your are using XML Query instead of sub query in SP?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-03-18T21:18:00

Almost certainly it's your "xml": '12345' isn't a valid XML document, so this code:

@p_CarId NVARCHAR(MAX) = '12345'
...
EXEC sp_xml_preparedocument @hdoc OUTPUT , @p_CarId

doesn't fit the parameter requirements unless your call to the SP provides valid XML file content.
So check that: look at exactly what you pass to your SP, and check it's XML content carefully. I suspect you don't pass a value at all, or mispelled the parameter name when you tried.

Quote:
Hi sir, thank you for your explanation. Due to some circumstances, i can't debug my codes on the actual input parameter. But based on your suggestion, i have altered my question and tried to execute it and the error is gone now albeit with no results returned. Could you please have a look on my updated question?

If I minimise your updated code:

DECLARE @xmlContent AS NVARCHAR(MAX)
    SET @xmlContent = '
    <ROOT>
	   <Vehicles CarId="12345">
	   </Vehicles>
    </ROOT>
    ';

    DECLARE 
	   @hdoc AS INT;  
    EXEC sp_xml_preparedocument @hdoc OUTPUT , @xmlContent 
    
    DECLARE @Car TABLE 
    (  
	   CarId BIGINT
    )   
      
   
    INSERT INTO 
	   @Car  
    SELECT   
	   *  
    FROM   
	   OPENXML(@hdoc,'/ROOT/Vehicles/CarId',1) WITH(CarId BIGINT)  
   SELECT   
	     c.CarId

And run it against SQL Server 2012 SP1, I get no error. I get no output either, but I don't get an error.
Changing to this:

OPENXML(@hdoc,'/ROOT/Vehicles',1) WITH(CarId BIGINT)

Gives me a single row containing "12345" as I'd expect.

So what happens when you "minify" your input to match mine?

Richard Deeming · Answer 2 · 2018-03-19T05:50:00

Since you're using a recent version of SQL Server, it would probably be easier to use the XML data type, rather than the OPENXML approach:
XML Data Type and Columns (SQL Server) | Microsoft Docs[^]

SQL

DECLARE @xmlContent xml = N'
<ROOT>
    <Vehicles CarId="' + CAST(@p_CarId As nvarchar(max)) + N'"/>
</ROOT>
';

DECLARE @Car TABLE 
(  
    CarId BIGINT
);

INSERT INTO @Car
(
    CarId
)
SELECT
    X.V.value('@CarId', 'bigint')
FROM
    @xmlContent.nodes('//Vehicles') As X (V)
;

But if you're just trying to pass multiple values to the parameter, you'd probably do better to use a Table-Valued parameter:
Use Table-Valued Parameters (Database Engine) | Microsoft Docs[^]

Santosh kumar Pithani · Answer 3 · 2018-03-19T19:53:00

SQL

DECLARE @p_CarId NVARCHAR(MAX) ='12345';
BEGIN
;WITH CTE AS(
          SELECT cd.value('@CarId','Bigint') AS CarId FROM (
		  SELECT  CAST('<ROOT> <Vehicles CarId="'+@p_CarId+'"> </Vehicles> 
                            </ROOT>' AS XML) xmldata
                                                           ) as tab 
		            CROSS APPLY xmldata.nodes('ROOT/*') AS col(cd)
			)
   SELECT   
	     c.CarId  
	    ,cd.CarModel
	    ,cd.CarManufacturedDate
    FROM  
	  CTE AS c
	   INNER JOIN CarDetails AS cd
		  ON c.CarId = cd.CarId 

END;

Invalid at the top level of the document

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0