1

I need to generated an xml to load on an internet site, from my SQL2005 DB. All work fine, but when the file is loaded on the webservice that need it, I've this error:

'.', hexadecimal value 0x00, is an invalid character. Line 1, position 41.

The xml code work fine, but I think the problem is in the file. The Xml is generated with this query

set @cmd = 'bcp "SELECT XML FROM ' + @DBNAME + '.dbo.FATTELETTR WHERE PROGRESSIVO LIKE ' +  @PROGRESSIVO + ' " queryout ' + @Filename + ' -U user -P pass -w -r -t -x';
--print @cmd

EXEC XP_CMDSHELL @cmd, no_output;

This is a file example: 

    <?xml version="1.0" encoding="UTF-8" ?> <p:FatturaElettronica versione="FPR12" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:p="http://ivaservizi.agenziaentrate.gov.it/docs/xsd/fatture/v1.2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://ivaservizi.agenziaentrate.gov.it/docs/xsd/fatture/v1.2 http://www.fatturapa.gov.it/export/fatturazione/sdi/fatturapa/v1.2/Schema_del_file_xml_FatturaPA_versione_1.2.xsd">
<FatturaElettronicaHeader>
    ................ content............
    </FatturaElettronicaHeader>
    <FatturaElettronicaBody>
    ................ content............
    </FatturaElettronicaBody></p:FatturaElettronica>

If I take directly the file generated, I've the error, INSTEAD if I take the xml content, create a new xml file, and paste the contents copied to the one manually created, all work fine and I've no error.

Also if I try to open on internet explorer the xml files, the sql generated one is empty, I see a blank page, instead the one manually created is loaded fine.

So the file has the problem. But which is?

S3S
  • 24,809
  • 5
  • 26
  • 45
BigBlack
  • 163
  • 2
  • 12

1 Answers1

0

Your XML file's encoding is declared to be UTF-8 <?xml version="1.0" encoding="UTF-8" ?>, but SQL Server doesn't support UTF-8. It uses UTF-16 for unicode data. So your file on disk is stored as UTF-16, 2 bytes per symbol, while you instructed the XML parser to read it as UTF-8, variable symbol length, 1 byte for basic Latin chars and digits. For example, in UTF-8 the letter A is encoded as one byte 41, in UTF-16 it is 2 bytes 0041. Most likely this leading 00 is the one causing the error.

You must either change the declared encoding for your XML file to be UTF-16, or change the way you save the XML data to match the declared encoding.

Andrey Nikolov
  • 12,967
  • 3
  • 20
  • 32
  • How can I change in sql the utf16 with utf8? Or what can I use to process the xml created and change from 16 to utf8? Another software? A batch file? I would use something to automate the process. – BigBlack Jan 23 '19 at 15:20
  • Your question doesn't tell how you generate this XML. Change it there. About converting the encoding you can see [UTF-16 to UTF-8 conversion](https://stackoverflow.com/questions/265370/utf-16-to-utf-8-conversion-for-scripting-in-windows) – Andrey Nikolov Jan 23 '19 at 15:28