Solved

Getting XML Parse error when run below query in SQL 2005

Posted on 2009-07-03
5
135 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server DeDupe Table 3 39
Need help with the proper SQL syntax when querying a linked Server 1 50
SQL Trigger selecting another database 4 34
Need help with a query 6 67
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

22 Experts available now in Live!

Get 1:1 Help Now