Sql Server 2014 - Parsing Xml With Cyrillic Characters

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

Solution 1:

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.

Solution 2:

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:

CREATEPROCEDURE dbo.TestXML(@xml XML)
ASBEGINSELECT@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.

Share