Solved

Getting XML Parse error when run below query in SQL 2005

Posted on 2009-07-03
5
134 Views
Last Modified: 2012-05-07
Getting XML Parse error when run below query:

DECLARE @XMLString XML,@newname varchar(5000)
SET @newname ='
<?xml version="1.0" encoding="utf-16"?>
<CustomFields xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <IDValue>
    <ID>22</ID>
    <VALUE>Staging</VALUE>
  </IDValue>
</CustomFields>'


SELECT CAST(CAST ((@newname) AS VARBINARY (MAX)) AS XML)
0
Comment
Question by:ravinder_mask
  • 2
5 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24772492
try this one:




DECLARE @XMLString XML,@newname varchar(5000)

SET @newname ='

<CustomFields xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <IDValue>

    <ID>22</ID>

    <VALUE>Staging</VALUE>

  </IDValue>

</CustomFields>'
 

SELECT CAST(CAST ((@newname) AS VARBINARY (MAX)) AS XML)

Open in new window

0
 

Author Comment

by:ravinder_mask
ID: 24811997
yes, it works but '<?xml version="1.0" encoding="utf-16"?>' this is generated by code at run time, I cannot remove it, so please suggest solution with this line. thanks is advance
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 25543776
I got this working:
§ utf-8 instead of utf-16
§ no spaces before the <?xml ... > stuff


DECLARE @XMLString XML,@newname varchar(5000)

SET @newname ='<?xml version="1.0" encoding="utf-8"?>

<CustomFields xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <IDValue>

    <ID>22</ID>

    <VALUE>Staging</VALUE>

  </IDValue>

</CustomFields>'
 

set  @XMLString = @newname
 

SELECT CAST(@newname  AS XML)

Open in new window

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 25543782
aka:
DECLARE @XMLString XML,@newname varchar(5000)

SET @newname ='

<?xml version="1.0" encoding="utf-16"?>

<CustomFields xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <IDValue>

    <ID>22</ID>

    <VALUE>Staging</VALUE>

  </IDValue>

</CustomFields>'
 

set  @XMLString = replace(ltrim(replace(replace(@newname, char(10), ''), char(13), '')), 'encoding="utf-16"' , 'encoding="utf-8"' )

select @xmlstring

-- SELECT CAST(@newname  AS XML)

Open in new window

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now