Read the multi languages from XML using OPENXML

-- NB: Using "N" prefix to indicate a Unicode string:
DECLARE @xml xml = N'<translation_list>
    <translation>
        <lang_code>EN</lang_code>
        <description>Consumer Fraud Suspected</description>
    </translation>
    <translation>
        <lang_code>HI</lang_code>
        <description>उपभोक्ता धोखाधड़ी संदिग्ध</description>
    </translation>
    <translation>
        <lang_code>RU</lang_code>
        <description>Обман потребителей Подозреваемый</description>
    </translation>
    <translation>
        <lang_code>ZH</lang_code>
        <description>欺骗消费者嫌疑</description>
    </translation>
</translation_list>';

-- Using sp_xml_preparedocument:
DECLARE @hDoc int;
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML;
 
SELECT 
    LANG_CODE, 
    DESCRIPTION
FROM 
    OPENXML(@hDoc, 'TRANSLATION_LIST/TRANSLATION')
    WITH
    (
        LANG_CODE nvarchar(50) 'LANG_CODE',
        DESCRIPTION nvarchar(max) 'DESCRIPTION'
   )
;
 
EXEC sp_xml_removedocument @hDoc;

/*
Output:

LANG_CODE       DESCRIPTION
EN              Consumer Fraud Suspected
HI              उपभोक्ता धोखाधड़ी संदिग्ध
RU              Обман потребителей Подозреваемый
ZH              欺骗消费者嫌疑
*/


-- Using XML type methods:
SELECT 
    Node.value('LANG_CODE[1]', 'char(2)') As LANG_CODE,
    Node.value('DESCRIPTION[1]', 'nvarchar(max)') As DESCRIPTION
FROM 
    @xml.nodes('//TRANSLATION') As List (Node)
;

/*
Output:

LANG_CODE       DESCRIPTION
EN              Consumer Fraud Suspected
HI              उपभोक्ता धोखाधड़ी संदिग्ध
RU              Обман потребителей Подозреваемый
ZH              欺骗消费者嫌疑
*/