0

The functionality is like, we could write the plane SQL queries in XML and then we could import that xml in Product to see the changes in database.

In Update statement, I need to use the below update query. But getting 'XML declaration must be the first node in the document' error when trying to set column value.

<?xml version="1.0" encoding="utf-8"?>
<Metadata ClientSchemaVersion="1.1" Name="DummyName">
    <Differences>
      <Updates>
      ---
      --- 
Begin 
Update TABLE_NAME
Set //In next line, error is comming
ColumnName='<?xml version=''1.0'' encoding=''utf-16le'' ?><scenario xmlns='Text'><id>12345</id><title>
--
--
--
WHERE Condition

    END
    </Differences>
 </Metadata>

How I could achieve that use case. Could someone help me out here.

Note- The same Update Query is working fine if ran in SQL Server directly

Kms
  • 1,082
  • 2
  • 11
  • 27
  • Remove XML declaration, SQL Server doesn't use it. You can read [docs.ms](https://learn.microsoft.com/en-us/sql/relational-databases/xml/xml-data-type-and-columns-sql-server?view=sql-server-ver15#xml-storage-options) – gofr1 Feb 24 '21 at 10:18
  • That XML declaration would be, at best, a lie. You're using a non-nvarchar string literal (`''` without `N` prefix) which means it's not in any unicode encoding, let alone `utf-16le`. – Damien_The_Unbeliever Feb 24 '21 at 11:04

1 Answers1

0

Two XML declarations (<?xml ... ?>) are not allowed in an XML document. Only one is permitted, and it must be at the very top of the document.

Here are two ways you can repair your XML:

  1. Remove the second XML declaration. The default for that SQL-embedded XML will then be XML version 1.0 with UTF-8 encoding.

  2. If the processing application supports it, wrap the SQL in a CDATA section such that the SQL-embedded XML document is no longer parsed as XML.

    <![CDATA[

   ... SQL with embedded XML that can contain its own XML declaration ...

]]>

See also

kjhughes
  • 106,133
  • 27
  • 181
  • 240