0

I've xml column in a table. When i'm inserting the xml into the table, all the symbols in xml are normal. But when i'm trying to insert a node into the xml using Xml DML statement, if that node has symbols like "Delta", SqlServer is converting them to question mark. Can anyone suggest how to resolve this issue ?

Here is the sample code :

-- 1. Create Table
CREATE TABLE SAMPLE_XML (ID INT IDENTITY, Information xml);

-- 2. Create a stored procedure to insert data into the table.

CREATE PROCEDURE [dbo].[SET_SAMPLEXML]
@Information xml
AS
BEGIN
    INSERT INTO SAMPLE_XML
           (Information)
     VALUES
           (@Information);
END

--3. execute stored procedure to insert data, delta symbol gets recognized
  EXEC  SET_SAMPLEXML
    @Information = N' <tr>                      
        <td>Δcardia info</td>            
    </tr>';

 --4 Now, insert one more node with delta symbol. SqlServer converts it into ? mark.
    UPDATE SAMPLE_XML
    SET Information.modify('insert (<td>Δinput</td>) as first into (tr)[1]') WHERE
    ID=1;

 --5 The result of the select statement of the table. Please observe, newly inserted td tag has the delta symbol converted into ? mark. 
    <tr>
<td>?input</td>
<td>Δcardia info</td>
</tr>
Sandhya
  • 3
  • 3

2 Answers2

3

This has nothing to do with XML. Your little triangle is a Unicode character, and to present that without losing data, you need to prefix your string with an N (which all dynamic SQL should be anyway, even if it doesn't contain known Unicode characters).

Compare:

SELECT [varchar] = 'Δ', [nvarchar] = N'Δ';

Results:

varchar   nvarchar
-------   --------
?         Δ
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
0

I've found the solution. I changed the xml.modify statement to use sql:variable and created a stored procedure for updating the content, it worked fine. Ofcourse, for dynamic SQL, data should be prefixed with 'N'.

CREATE PROCEDURE [dbo].[MODIFY_SAMPLEXML]

@Information xml

AS BEGIN

UPDATE SAMPLE_XML SET Information.modify('insert sql:variable("@Information") as first into (tr)[1]') WHERE ID=1;

END

Sandhya
  • 3
  • 3