“unable to switch the encoding” error when “<?xml version=”1.0“ encoding=”UTF-16“ standalone=”no“ ?>” at XML beginning

While shredding the XML in SQL this way:

DECLARE @XML2 XML
SET @XML2 = '
<App_MemberServers>
<memberServers>
<client _type_="3" clientId="3" clientName="TokyoProxy" clientSidePackage="1" consumeLicense="1" srmReportSet="0" srmReportType="0" type="0"/>
</memberServers>
</App_MemberServers>'

select a.b.value('@clientId','INT') ClientID
from @XML2.nodes('/App_MemberServers/memberServers/client') a(b)

It works fine, but when the XML is

  • Stored Procedure calls multiple stored procedures
  • How to join linked server table and sql server table while using openquery
  • How do I reset an increment identity's starting value in SQL Server
  • SQL Server ORDER BY date and nulls last
  • UPDATE Stored Procedure not Updating
  • revoke vs deny : what is the difference
  • DECLARE @XML2 XML
    SET @XML2 = '<?xml version="1.0" encoding="UTF-16" standalone="no" ?>
    <App_MemberServers>
    <memberServers>
    <client _type_="3" clientId="3" clientName="TokyoProxy" clientSidePackage="1" consumeLicense="1" srmReportSet="0" srmReportType="0" type="0"/>
    </memberServers>
    </App_MemberServers>'
    
    select a.b.value('@clientId','INT') ClientID
    from @XML2.nodes('/App_MemberServers/memberServers/client') a(b)
    

    I get the following issue “unable to switch the encoding”

    Why? Please help

  • Total Sum and Partial Sum
  • How to make SET XACT_ABORT ON rollback the transaction?
  • How to display products under Category in sql in a table
  • inserting data into a table that has a many to many relationship
  • When does ODBC closes a connection after doing a SQLFreeHandle?
  • Explicitly drop temp table or let SQL Server handle it
  • One Solution collect form web for ““unable to switch the encoding” error when “<?xml version=”1.0“ encoding=”UTF-16“ standalone=”no“ ?>” at XML beginning”

    because your string is not in utf-16. add N before string literal to make it UTF:

    DECLARE @XML2 XML
    SET @XML2 = N'<?xml version="1.0" encoding="UTF-16" standalone="no" ?>
    <App_MemberServers>
    <memberServers>
    <client _type_="3" clientId="3" clientName="TokyoProxy" clientSidePackage="1" consumeLicense="1" srmReportSet="0" srmReportType="0" type="0"/>
    </memberServers>
    </App_MemberServers>'
    
    select a.b.value('@clientId','INT') ClientID
    from @XML2.nodes('/App_MemberServers/memberServers/client') a(b)
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.