CODEWITHSUNDEEP
sql-server
Thomas
Thomas

Reputation: 34188

The XML parse error 0xc00ce01d occurred

i am getting xml parser error. how to handle this situation.

my script was

DECLARE @xmlDoc VARCHAR(MAX)
DECLARE @handle INT
SET @xmlDoc = N'
<DELETED>
  <JID>41185</JID>
  <WID>0</WID>
  <AccountReference>LH169</AccountReference>
  <OEReference>Ari002</OEReference>
  <InvoiceNumber>0</InvoiceNumber>
  <OrderPlacedBy>Mark Catterall</OrderPlacedBy>
  <Specialist>0FFICINA MOTTAUTO</Specialist>
  <UserName xsi:nil="true" />
</DELETED>'

Declare @SqlQuery varchar(max)

EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc
SET @SqlQuery ='Select '
SELECT  @SqlQuery = @SqlQuery + LocalName  + ',' FROM OPENXML (@handle, '/DELETED', 1) 
WHere  localname not like '#%' And id>0 And localname <> 'nil'
Set @SqlQuery = Substring(@SqlQuery,0,LEN(@SqlQuery)-1) + ' From tablename'
print @SqlQuery
--Exec @SqlQuery
--EXEC sp_xml_removedocument @handle

i am getting error for <UserName xsi:nil="true" /> how to handle this situation. what to rectify in the script.

thank

Upvotes: 0

Views: 1245

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

0xC00CE01D: Reference to undeclared namespace prefix.

Changing the XML to this will take care of that

<DELETED xmlns:xsi="uri">
  <JID>41185</JID>
  <WID>0</WID>
  <AccountReference>LH169</AccountReference>
  <OEReference>Ari002</OEReference>
  <InvoiceNumber>0</InvoiceNumber>
  <OrderPlacedBy>Mark Catterall</OrderPlacedBy>
  <Specialist>0FFICINA MOTTAUTO</Specialist>
  <UserName xsi:nil="true" />
</DELETED>'

Using the XML data type you could do this instead:

declare @xml xml = 
'<DELETED xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <JID>41185</JID>
  <WID>0</WID>
  <AccountReference>LH169</AccountReference>
  <OEReference>Ari002</OEReference>
  <InvoiceNumber>0</InvoiceNumber>
  <OrderPlacedBy>Mark Catterall</OrderPlacedBy>
  <Specialist>0FFICINA MOTTAUTO</Specialist>
  <UserName xsi:nil="true" />
</DELETED>'

declare @SqlQuery varchar(max)
set @SqlQuery ='Select '

select 
  @SqlQuery = @SqlQuery + N.value('local-name(.)', 'varchar(max)')+','
from @xml.nodes('DELETED/*') as T(N)

set @SqlQuery = substring(@SqlQuery,0,len(@SqlQuery)-1) + ' from tablename'

print @SqlQuery

Upvotes: 1

Related Questions