SQL Server 2014 - Parsing XML with Cyrillic Characters

Question

I'm parsing an xml file but have problem with cyrillic characters:

this is the relevant part of the stored Procedure

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>'

Stored Procedure

 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

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 ???

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)')

but I do not know how solve it.

Can suggest a solution?

Thanks

Here's a related answer: http://stackoverflow.com/a/41014697/5089204 — Shnugo, Mar 08 '17 at 21:08

Solomon Rutzky · Accepted Answer · 2017-03-08T22:11:24.363

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.

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.

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;

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.

Hi srutzky, actually I have no @XML declaration as stated, now I modified the Stored Procedure as it is, sorry for mistake — Joe, Mar 08 '17 at 21:52
@Joe You are still missing the capital `N` prefix on the input string. I have updated my answer with an adaptation of your updated code showing how this works. — Solomon Rutzky, Mar 08 '17 at 22:12
Sorry but I do not understand.. I do not declare @XML as a variable but as input field, therefore shall I write ` CREATE PROCEDURE dbo.spXML_ParseSOAP ( @XML XML = N'' )` ? Since I tried already but result.. is same — Joe, Mar 08 '17 at 22:41
@Joe how is the stored procedure being called? and from where, exactly? — Solomon Rutzky, Mar 08 '17 at 23:37
Hi, Sorry for late answer. Well SP is called from an asp page, but now it seems working, actually it works.. but I cannot see what I've changed :-( Thanks a lot! — Joe, Mar 09 '17 at 15:19

score 1 · Answer 2 · answered Mar 08 '17 at 23:05

First of all: If this solves your problem, please accept srutzky's answer, it is the correct answer to solve your initial example with the declared variable. (but you may vote on this :-) ).

This is just an example to show the problem:

Try this

SELECT 'Луговой проезд'
SELECT N'Луговой проезд'

And now try this:

CREATE PROCEDURE dbo.TestXML(@xml XML)
AS
BEGIN
    SELECT @xml;
END
GO

EXEC dbo.TestXML '<root><Street>Луговой проезд дом 4 корпус 1 квартира 12</Street></root>';

returns

<root>
  <Street>??????? ?????? ??? 4 ?????? 1 ???????? 12</Street>
</root>

While this call (see the leading "N")

EXEC dbo.TestXML N'<root><Street>Луговой проезд дом 4 корпус 1 квартира 12</Street></root>';

returns

<root>
  <Street>Луговой проезд дом 4 корпус 1 квартира 12</Street>
</root>

Conclusio

This does not happen within your procedure. The string you pass over to the stored procedure is wrong before you even enter the SP.

SQL Server 2014 - Parsing XML with Cyrillic Characters

2 Answers2

Conclusio

Linked