CODEWITHSUNDEEP
sql-serverxml
bbsimonbb
bbsimonbb

Reputation: 29002

How can I insert from a TEXT column to an XML column in SQL Server 2014

I have XML docs stored in a TEXT column (collation_name French_CI_AS, character_set_name iso_1).

I want to move them to a new table, in an XML column with the following SQL...

INSERT INTO Signature(JustifId, SignedJustif)
   SELECT JustifID, CONVERT(XML, Justif.SignedJustif,2)
   FROM Justif

When I do this, I get character encoding errors, that point to the high ascii character in this fragment "presentación, OU=CERES, O=FNMT-RCM, C=ES" - a spanish accented o in an X509 certificate.

This ó started life in utf8, became utf16 as a .net string, then became iso_1 when inserted into the TEXT column. I can copy and paste it into a web page no problem. How, then, do I move it from a TEXT column to an XML column in the same DB (and why is this so difficult?)?

The CONVERT idea came from this post. This MS page covers creating XML from varchar and nvarchar.

Upvotes: 0

Views: 434

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

This is tricky... A conversion on byte-level might lead to unexpected results...

Try this

INSERT INTO Signature(JustifId, SignedJustif)
SELECT JustifID, CONVERT(XML, CONVERT(VARCHAR(MAX),Justif.SignedJustif))
FROM Justif

If you still get issues, try to specify the specific collation together with the conversion and/or try to convert to NVARCHAR(MAX).

If this doesn't help, please edit your question and poste a (reduced) example. Best was a test-scenario with a minimal XML, where one can reproduce the error.

Upvotes: 2

Related Questions