sql server - Unicode in the SQL Stored Procedure

Question

The datatable received from the stored procedure contains a XML column. That XML column contains unicode data (For example: 顾客名称). Since the literal in the XML is not prefixed with N'. How to get the unicode data and store it in a variable?

question from:https://stackoverflow.com/questions/66059163/unicode-in-the-sql-stored-procedure

Answer 1

You can just simply add N before the XML Column just below & Assign this value to a Variable.

DECLARE @XML XML=N'顾客名称' --Add your Column Name
SELECT @XML 

DECLARE @XML NVARCHAR(50)=N'顾客名称'
SELECT @XML 

Inserting

INSERT INTO TableName ([Col_Name]) VALUES (N'<Value>')

XML Parsing

DECLARE @XML_ XML=N'<Event><Name><Action>顾客名称</Action></Name></Event>' -- add "N" here

SELECT T.item.value('(Name)[1]', 'NVARCHAR(MAX)') AS [Unicode]
FROM @XML_.nodes('/Event') AS T(item)

---

Using Table Insertion

DECLARE @T TABLE (         --- Creating 1st Tables
[NAME] NVARCHAR(255))

DECLARE @AnotherTable TABLE (   --- Creating 2nd Tables
[2nd Table NAME] NVARCHAR(255))

DECLARE @xml_ XML=N'<Event><Name><Action>顾客名称</Action></Name></Event>' -- add "N" here

INSERT INTO @T                 ----Inserting Data into 1st Table
SELECT T.item.value('(Name)[1]', 'NVARCHAR(MAX)') 
FROM @XML_.nodes('/Event') AS T(item)

SET @xml_=(SELECT NAME FROM @T FOR XML PATH('Columns'), TYPE)

INSERT INTO @AnotherTable      ----Inserting Data into 2nd Table
SELECT T.item.value('(NAME)[1]', 'NVARCHAR(MAX)') 
FROM @XML_.nodes('/Columns') AS T(item)

SELECT * FROM @T
SELECT * FROM @AnotherTable

---