SQL Server 2014-使用西里尔字符解析XML [英] SQL Server 2014 - Parsing XML with Cyrillic Characters
问题描述
我正在解析xml文件,但是西里尔字符有问题:
I'm parsing an xml file but have problem with cyrillic characters:
这是存储过程的相关部分
this is the relevant part of the stored Procedure
要解析的SOAP输入:
SOAP input to parse:
'<?xml version="1.0"?>
<soapenv:Envelope xmlns:.......>
<soapenv:Header>
</soapenv:Header>
<soapenv:Body>
<GetResponse>
<BuyerInfo>
<Name>Polydoros Stoltidys</Name>
<Street>Луговой проезд дом 4 корпус 1 квартира 12</Street>
</BuyerInfo>
</GetResponse>
</soapenv:Body>
</soapenv:Envelope>'
存储过程
CREATE PROCEDURE dbo.spXML_ParseSOAP
(
@XML XML
)
AS
SET NOCOUNT ON;
DECLARE @S nvarchar(max)='',
@C nvarchar(max)='',
@D nvarchar(max)=''
SELECT
@C= IIF (CHARINDEX('['+T.X.value('local-name(.)', 'nvarchar(100)')+']',@C)=0, CONCAT( ISNULL(@C + ',','') , QUOTENAME(T.X.value('local-name(.)', 'nvarchar(100)'))), @C),
@D= IIF (CHARINDEX('['+T.X.value('local-name(.)', 'nvarchar(100)')+']',@CP)=0, CONCAT( ISNULL(@D + ',N','') , '''', T.X.value(N'text()[1]', 'nvarchar(max)'),''''), @D),
FROM @XML.nodes('//*[count(child::*) = 0]') AS T(X)
WHERE T.X.value(N'local-name(.)', 'nvarchar(500)')
IN (select name from Customers.sys.columns where [object_id]=@O and is_identity=0)
SET @S=N'INSERT INTO Sales.dbo.ShippingAddress ('+@C+',ShippingAddressID) VALUES ('+@D+','''+@FADR+''')
Print @S
问题是@S看起来像这样
the problem is that @S looks like this
INSERT INTO Sales.dbo.ShippingAddress ([Name],[Street1],ShippingAddressID)
VALUES
(N'Polydoros Sample',N'??????? ?????? ??? 4 ?????? 1 ???????? 12','KkQ0LhbhwXfzi+Ko1Ai6s+SDZRT2kYhYC3vM2x2TB5Y=')
其中西里尔字母转换为???
where Cyrillic Charachters are transformed into ???
我把N放在所有输入之前,但问题显然在前面:
我想是在
I put the N before all input but problem is clearly before: I can suppose is in the
T.X.value(N'text()[1]', 'nvarchar(max)')
但我不知道如何解决。
可以提出解决方案吗?
谢谢
推荐答案
您的 DECLARE @XML
行是错误。字符串文字需要以大写字母 N
作为前缀。字符将转换为?
Your DECLARE @XML
line is wrong. The string literal needs to be prefixed with a capital N
. The characters are getting converted to ? in the interpretation of that literal.
此外,您还没有在所有字符串文字前加上大写的 N
前缀,但是您至少要有一个前缀( SET @S = N'
行中的第一个前缀,其余的字面量( VARCHAR
不带 N
前缀)将隐式转换为 NVARCHAR
。
Also, you have not prefixed all string literals with a capital-N
, but you have at least one of them prefixed (the first one in the SET @S = N'
line, and so the rest of the literals (which are VARCHAR
without the N
prefix) will be implicitly converted to NVARCHAR
.
对更新后的代码进行的以下修改显示了此行为,以及如何在输入字符串上放置 N
前缀(
The following adaptation of your updated code shows this behavior, and how placing the N
prefix on the input string (prior to calling the Stored Procedure) fixes the problem:
DECLARE @XML XML = N' <!-- remove the N from the left to get all ???? for "Street"-->
<BuyerInfo>
<Name>Polydoros Stoltidys</Name>
<Street>Луговой проезд дом 4 корпус 1 квартира 12</Street>
</BuyerInfo>
';
DECLARE @S nvarchar(max)='',
@C nvarchar(max)='Street',
@D nvarchar(max)=''
SELECT
@D= IIF (T.X.value('local-name(.)', 'nvarchar(100)') = N'Street',
T.X.value('./text()[1]', 'nvarchar(100)'),
@C)
FROM @XML.nodes('//*[count(child::*) = 0]') AS T(X)
SET @S=N'INSERT INTO Sales.dbo.ShippingAddress ('
+ @C+',ShippingAddressID) VALUES (N'''+@D+''',''a'') '
Print @S;
此外,SQL Server XML从未存储<?xml。 ..?>
声明行,因此您最好将其从文字值的开头删除。
Also, SQL Server XML does not ever store the <?xml ... ?>
declaration line, so you might as well remove it from the beginning of the literal value.
这篇关于SQL Server 2014-使用西里尔字符解析XML的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!