Xml parsing error sql

Xml parsing error in sql

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

SQL sp_xml_preparedocument — XML parsing error: An Invalid character was found in text content

I am sending an xml document to Sql Server as text and this error occurs only when sp_xml_preparedocument is called with special characters(öß) in the xml.

I am using OPENXML to extract the data from the elements to store in tables, however SQL Servers sp_xml_preparedocument cause an exception with special characters.

As a temp fix,
I solved this issue by changeing the sql stored procedure parameter that accepts the XML document to ntext type and changed the table columns type to nvarchar. This allowed the special characters to be inserted without errors into SQL

Required Solution
Do i have any other solution to stayback with my text and varchar datatype rather than using ntext and nvarchar?

Источник

Xml parsing error in sql

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

SQL sp_xml_preparedocument — XML parsing error: An Invalid character was found in text content

I am sending an xml document to Sql Server as text and this error occurs only when sp_xml_preparedocument is called with special characters(öß) in the xml.

I am using OPENXML to extract the data from the elements to store in tables, however SQL Servers sp_xml_preparedocument cause an exception with special characters.

As a temp fix,
I solved this issue by changeing the sql stored procedure parameter that accepts the XML document to ntext type and changed the table columns type to nvarchar. This allowed the special characters to be inserted without errors into SQL

Required Solution
Do i have any other solution to stayback with my text and varchar datatype rather than using ntext and nvarchar?

Источник

Xml parsing error in sql

Asked by:

Question

I have splitted mt data using this code.

create function dbo.srs_stringtorow(@email varchar(100)) returns @data table(email varchar(150))
as
begin
declare @xml xml, @s nvarchar(max)
set @xml = N’ ‘ + REPLACE( replace(@email,’,’,’ ‘),’;’,’ ‘) + ‘ ‘
insert into @data
select * from(
select
t.value(‘.’,’varchar(150)’) as [delimited items]
from @xml.nodes(‘//root/r’) as a(t)) r

create table #tt (email varchar(300))
insert into #tt
select top 5000 * from emailid —order by desc

select distinct d.* from #tt
cross apply dbo.srs_stringtorow(#tt.email) d
where len(d.email)>1

but i have 200000 entries and its working for 4000 entries and if i use more than that i get the following error

XML parsing: line 1, character 55, illegal qualified name character

Источник

Xml parsing error in sql

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I’m parsing multiple values and spliting them with ‘,,‘, but when I have ‘&’ character into any of the value my sql script is failing up, can anybody help me with this to handle ‘&’ characters too, here is the my code:-

Thanks in advance

Please do let us know your feedback. Thank You — KG, MCTS

Answers

There are several characters that have special meaning inside an xml document, and «&» is one of them. You can use FOR XML PATH to get the correct representation of those characters, before the xml casting.

• Edited by Hunchback MVP Friday, December 9, 2011 3:30 PM
• Marked as answer by gk1393 Friday, December 9, 2011 4:52 PM

I suggest you to take a moment to review Alejandro’s answer or a blog reference I provided. Brad tells in his blog:

One might be tempted to use multiple REPLACE() functions to convert all ampersands to & and less-thans to , but who knows what others characters are out there that will create problems? We already have a tool at our disposal that will encode the characters for us… the FOR XML clause.

We learned in my previous blog entry that we can eliminate tags being created by explicitly specifying a column a name of [*]. If we hadn’t done that, then we would end up with our lists surrounded by tags. And we eliminated yet another tag surrounding our data by passing the empty string to FOR XML PATH. So our lists are really the same as they were originally, except for the special characters being automatically encoded for us.

Now we can incorporate that CROSS APPLY into our query:

For every expert, there is an equal and opposite expert. — Becker’s Law

Источник

Xml parsing error in sql

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

SQL sp_xml_preparedocument — XML parsing error: An Invalid character was found in text content

I am sending an xml document to Sql Server as text and this error occurs only when sp_xml_preparedocument is called with special characters(öß) in the xml.

I am using OPENXML to extract the data from the elements to store in tables, however SQL Servers sp_xml_preparedocument cause an exception with special characters.

As a temp fix,
I solved this issue by changeing the sql stored procedure parameter that accepts the XML document to ntext type and changed the table columns type to nvarchar. This allowed the special characters to be inserted without errors into SQL

Required Solution
Do i have any other solution to stayback with my text and varchar datatype rather than using ntext and nvarchar?

Источник

I was running this query in SQL Server 2008 (10.0.5890),
Collation: SQL_Latin1_General_CP1_CI_AS

SELECT
   CAST(event_data.value('(event/data/value)[1]',
   'NVARCHAR(max)') AS XML) AS DeadlockGraph 
FROM
   ( SELECT
      XEvent.query('.') AS event_data 
   FROM
      ( -- Cast the target_data to XML SELECT
         CAST(target_data AS XML) AS TargetData 
      FROM
         sys.dm_xe_session_targets st 
      JOIN
         sys.dm_xe_sessions s 
            ON s.address = st.event_session_address 
      WHERE
         name = 'system_health' 
         AND target_name = 'ring_buffer' ) AS Data -- Split out the Event Nodes CROSS APPLY TargetData.nodes('RingBufferTarget/ event[@name="xml_deadlock_report"]') AS XEventData ( XEvent ) ) AS tab ( event_data )

But it was always ended with the following error:

Msg 9420, Level 16, State 1, Line 1
XML parsing: line 22203, character 102, illegal xml character

Even if I changed it to VARCHAR(max) it remains the same.
Though this same script was running fine and without any issue in another server with the same version & collation.

Sample XML result from the working database:

Proc [Database Id = 5 Object Id = 2033194789]

    </process>
    <process id="process55d288" taskpriority="0" logused="584" waitresource="KEY: 5:72057594332250112 (6b0281c999d7)" waittime="1281" ownerId="1679" transactionname="EventGetQueueTopSp" lasttranstarted="2016-07-02T20:08:56.350" XDES="0x8209f990" lockMode="U" schedulerid="4" kpid="2880" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-07-02T20:09:12.370" lastbatchcompleted="2016-07-02T20:08:59.090" lastattention="1900-01-01T00:00:00.090" clientapp="EventService Queue Check" hostname="WVERPDB" hostpid="3148" loginname="sa" isolationlevel="read uncommitted (1)" xactid="1679" currentdb="5" lockTimeout="4294967295" clientoption1="671221856" clientoption2="128056">
      <executionStack>
        <frame procname="" line="96" stmtstart="8066" stmtend="8714" sqlhandle="0x0300050009a8007d9eadc300449e00000100000000000000" />
      </executionStack>
      <inputbuf>
</deadlock>

Here is the SQL version for both DB

Microsoft SQL Server 2008 (SP3) - 10.0.5890.0 (X64)   Apr  2 2015 16:07:19   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)