Solved

XML: The XML parse error 0xc00ce505 occurred on line number 3, near the XML text "

Posted on 2011-03-09
2
2,039 Views
Last Modified: 2012-05-11
i have this store procedure
create procedure usp_chk (@xmldoc nvarchar(100))
as

begin
      declare @hind int

      exec sp_xml_preparedocument @hind output, @xmldoc
      select *
      from openxml (@hind,'Root/Location/Item',2)
      with
            (
                  LocID int '../@LocID',
                  ItemID int,
                  ItemDesc varchar(50),
                  Qty int
            )
      exec sp_xml_removedocument @hind

end

when i test
--Testing
go
declare @xmldoc nvarchar(1000)
set @xmldoc = '<Root>
<Location LocationID="1" LocName="East Warehouse">
      <Item ItemID="22" ItemDesc="Keyboard" Qty="120">
      </Item>
      <Item ItemID="27" ItemDesc="Flat Screen" Qty="72">
      </Item>
</Location>
</Root>'

exec usp_chk  @xmldoc

i have this error
The XML parse error 0xc00ce505 occurred on line number 3, near the XML text "      <Item ItemID="22" ItemDesc="Keyboard" Q".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'A name contained an invalid character.'.
Msg 8179, Level 16, State 5, Procedure usp_chk, Line 7
Could not find prepared statement with handle 0.
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

0
Comment
Question by:enrique_aeo
  • 2
2 Comments
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 250 total points
ID: 35096058
In your sproc declaration, you did not make the incoming parameter large enough to accommodate the incoming XML string. You declared it to be 100 in size, but the string being sent in is larger than 100. Making this value larger (e.g. make it match the 1000 you defined for the test parameter) should resolve this.

You also have a couple of errors in your sproc logic. I believe you need to use "1" instead of "2" on your OPENXML call. Also, your selection for LocationID is incorrect. You put "@LocID", but it should be "@LocationID."
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 250 total points
ID: 35096098
Here's a demonstration:
untitled.PNG
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question