XML string to xml unable to switch encoding

Question

I have an XML string that is malformed.

DECLARE @xmlt TABLE(xstr nvarchar(max), xml xml)

INSERT INTO @xmlt(xstr) VALUES (
'  <?xml version="1.0" encoding="windows-1257" ?>
- <objects><object id="778913">a</object>
- <object id="785491">b</object>
- <object ...goes on...
- </objects>
'

To be able to use that XML I convert it to XML

UPDATE @xmlt SET xml = CAST(REPLACE(LTRIM(xstr), ' - <', '<') AS xml);

But I get an error XML parsing: line 1, character 46, unable to switch the encoding.

Is there any other way (without replacing the string encoding="windows-1257" with "") to convert that XML string to xml in SQL Server?

Remark: The Xml in you sample doesn't have a root node over all the <object> nodes, it's not valid — Jonathan Larouche
– Jonathan Larouche, Commented May 21, 2015 at 11:48
Good comment, :) thanks! The xml actually has it. Updated the example. — Developer Marius Žilėnas
– Developer Marius Žilėnas, Commented May 21, 2015 at 11:52

Jonathan Larouche · Accepted Answer · 2015-05-21 12:02:21Z

2

The field xstr nvarchar(max) is not compatible with encoding windows-1257. If you use varchar(max) it won't fail at converting the string into an XML. Varchar type & Xml encoding are related. If your XML Encoding is unicoded (e.g. UFT-16) then the use of nvarchar would have worked.

This will work with your xml string:

DECLARE @xmlt TABLE(xstr varchar(max), xml xml)

insert into @xmlt(xstr) values( '<?xml version="1.0" encoding="windows-1257"?>
- <objects>
- <object id="778913">a</object>
- <object id="785491">b</object>
- </objects>')

update @xmlt set xml = cast ( REPLACE(ltrim(xstr),' - <','<') as xml)

answered May 21, 2015 at 12:02

Jonathan Larouche

9927 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

XML string to xml unable to switch encoding

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related