Getting error while reading XML with OPENXML

Question

I am reading XML data using OPENXML in a stored procedure in SQL Server 2012.

My XML:

<name>
    <name>Temp1</name>
    <data>
        <mealID>5</mealID>
        <food>
            <foodID>11</foodID>
        </food>
        <food>
            <foodID>12</foodID>
        </food>
    </data>
    <data>
        <mealID>6</mealID>
        <food>
           <foodID>13</foodID>
        </food>
    </data>
</name>

My stored procedure:

ALTER PROCEDURE [dbo].[sp_Insert_EatingProgramTemplate] 
    @Template xml,
    @intOutSuccess nvarchar(10) OUTPUT,
    @ErrorSeverity nvarchar(MAX) OUTPUT,
    @ErrorMesg nvarchar(MAX) OUTPUT
AS
BEGIN
    DECLARE @idoc int

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SET @ErrorSeverity = ''
    SET @ErrorMesg = ''
    SET @intOutSuccess = ''

    BEGIN TRY
        SELECT *
         FROM OPENXML (@idoc, '/ROOT/Customer',1)
         WITH (CustomerID  varchar(10),
               ContactName varchar(20))
        SET @intOutSuccess = 1
    END TRY
    BEGIN CATCH
        SET @ErrorSeverity = ERROR_SEVERITY()  
        SET @ErrorMesg = ERROR_MESSAGE() 
        SET @intOutSuccess = -1
        GOTO A
    END CATCH
A:
    END
GO

While executing this procedure I am getting this error:

Could not find prepared statement with handle 0

Any help, why I am getting this error?

Well, did you do any debugging? Like do you expect a certain value for @idoc? Did you output it to see if it contained the value you expected? Also, did you consider just querying the XML directly instead of using OPENXML, sp_xml_preparedocument, etc.? — Aaron Bertrand
– Aaron Bertrand, Commented Apr 30, 2015 at 13:00
yes i tried it with XML directly but getting error. I did not do any debugging for this — Twix
– Twix, Commented Apr 30, 2015 at 13:02
@Twix your OPENXML query completely unrelated to the XML structure you have. What values do you actually want to get from that XML sample? Anyway, I'd suggest to look into nodes() XML function instead — har07
– har07, Commented Apr 30, 2015 at 13:41
Just curious, what is the point of putting a goto in there? Also, you shouldn't use the sp_ prefix for procedure names. Here is a link to an article by @AaronBertrand sqlperformance.com/2012/10/t-sql-queries/sp_prefix — Sean Lange
– Sean Lange, Commented Apr 30, 2015 at 13:42
@har07 I know I have provided different select statement but that is what I tried on different XML and I could not relate it with my XML and don't know how to use it — Twix
– Twix, Commented May 1, 2015 at 4:44

Andriy M · Accepted Answer · 2015-04-30 14:27:07Z

2

You missed to set @idoc by calling sp_xml_preparedocument, for example :

declare @template xml = '<name>
    <name>Temp1</name>
    <data>
        <mealID>5</mealID>
        <food>
            <foodID>11</foodID>
        </food>
        <food>
            <foodID>12</foodID>
        </food>
    </data>
    <data>
        <mealID>6</mealID>
        <food>
           <foodID>13</foodID>
        </food>
    </data>
</name>'
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @template; 

SELECT *
     FROM OPENXML (@idoc, '/name/data/food/foodID',1)
     WITH (FoodID  INT '.',
           MealID INT '../../mealID')

--clear parsed XML document from SQL Server cache
EXEC sp_xml_removedocument @idoc;

You can also achieve the same output by querying the xml variable directly :

SELECT 
    x.value('.', 'int') as FoodID
    , x.value('../../mealID[1]', 'int') as MealID
FROM @template.nodes('/name/data/food/foodID') as T(X)

edited Apr 30, 2015 at 14:27

Andriy M

78k18 gold badges100 silver badges157 bronze badges

answered Apr 30, 2015 at 14:01

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andriy M Over a year ago

Shouldn't you also apply sp_xml_removedocument if you use sp_xml_preparedocument?

har07 Over a year ago

@AndriyM thanks for the hints, I didn't know initially, but MSDN suggests that I should

Collectives™ on Stack Overflow

Getting error while reading XML with OPENXML

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related