none
SQL Server SSIS Export as XML Error: Name cannot begin with the '.' character, hexadecimal value 0x00. Line 1, position 40 RRS feed

  • Question

  • I'm using SQL Server 2014 and SSIS. I have an OLE DB source using the below SQL command to output to a flat file destination - that is configured to save as .XML

    This is my SQL:

    DECLARE @XMLOutput XMLDECLARE @XMLOutputChar nvarchar(max);WITH XMLNAMESPACES('myNameSpace' as ns)SELECT @XMLOutput =(SELECT field1 FROM   table1     FOR XML PATH('Testing'),TYPE, ROOT('TestingLoader'),ELEMENTS XSINIL)SET @XMLOutputChar = '<?xml version="1.0" encoding="UTF-8"?>' + CONVERT(nvarchar(max),@XMLOutput)SELECT @XMLOutputChar AS XMLOutput


    However, when I try to import the XML file into a 3rd party application I receive the error:

    Name cannot begin with the '.' character, hexadecimal value 0x00. Line 1, position 40.

    Even if I try to view in MS Word I receive the error:

    Unable to switch the encoding

    Is this because I'm defining it as UTF-8 and nvarchar(max)?

    Thanks.


    Tuesday, July 30, 2019 11:49 AM

Answers

  • Hi ChairmanMichael,

    SQL Server is always using the UTF-16 encoding internally. Additionally, the explicit prolog is stripped out from any variable or column of XML data type.

    "...a 3rd party application I receive the error...", what application are you using? You can always open any XML file in the browser to check if it is a well-formed XML.

    1. It seems that you modified/simplified your original SQL to obfuscate some values.
      Please provide the real life example.
    2. Unfortunately, SSIS still doesn't have XML Destination Adapter. I was asking for it since 2004, even before the very first version SSIS 2005 was released.
    3. That's why it is better to use bcp utility to generate XML file on the file system.
      This method is very fast, and there is no string manipulations, pure XML is streamed to the file system:

    DECLARE @SQLCmd VARCHAR(8000)
    	, @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml'
    	, @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe'
    	, @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');';
    
    -- SQL Server 2016 and later (it works even in 2012 but undocumented)
    SET @SQLCmd = FORMATMESSAGE('START "" "%s" "%s" queryout "%s" -T -x -c -C 1252 -a 32768 -S %s'
    				, @bcp
    				, @SQL
    				, @outputFileName
    				, @@SERVERNAME);
    
    -- just to see it
    SELECT @SQLCmd AS [Command to execute];
    
    -- create the XML file on the file system
    EXECUTE master.sys.xp_cmdshell @SQLCmd;

    Simulation of your SQL, and it is working:

    DECLARE @tbl TABLE(field1 VARCHAR(30)); INSERT INTO @tbl VALUES ('some value'); DECLARE @XMLOutput XML; DECLARE @XMLOutputChar nvarchar(max) ;WITH XMLNAMESPACES('myNameSpace' as ns) SELECT @XMLOutput = ( SELECT field1 FROM @tbl FOR XML PATH('Testing'),TYPE, ROOT('TestingLoader'),ELEMENTS XSINIL ); SET @XMLOutputChar = '<?xml version="1.0" encoding="UTF-16"?>' + CONVERT(nvarchar(max),@XMLOutput); SELECT @XMLOutputChar AS XMLOutput;

    SELECT CAST(@XMLOutputChar AS XML);


    Output as NVARCHAR:

    XMLOutput
    <?xml version="1.0" encoding="UTF-16"?><TestingLoader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns="myNameSpace"><Testing><field1>some value</field1></Testing></TestingLoader>

    Output as XML:

    <TestingLoader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns="myNameSpace">
      <Testing>
        <field1>some value</field1>
      </Testing>
    </TestingLoader>
    Tuesday, July 30, 2019 2:19 PM

All replies

  • Hi ChairmanMichael,

    SQL Server is always using the UTF-16 encoding internally. Additionally, the explicit prolog is stripped out from any variable or column of XML data type.

    "...a 3rd party application I receive the error...", what application are you using? You can always open any XML file in the browser to check if it is a well-formed XML.

    1. It seems that you modified/simplified your original SQL to obfuscate some values.
      Please provide the real life example.
    2. Unfortunately, SSIS still doesn't have XML Destination Adapter. I was asking for it since 2004, even before the very first version SSIS 2005 was released.
    3. That's why it is better to use bcp utility to generate XML file on the file system.
      This method is very fast, and there is no string manipulations, pure XML is streamed to the file system:

    DECLARE @SQLCmd VARCHAR(8000)
    	, @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml'
    	, @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe'
    	, @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');';
    
    -- SQL Server 2016 and later (it works even in 2012 but undocumented)
    SET @SQLCmd = FORMATMESSAGE('START "" "%s" "%s" queryout "%s" -T -x -c -C 1252 -a 32768 -S %s'
    				, @bcp
    				, @SQL
    				, @outputFileName
    				, @@SERVERNAME);
    
    -- just to see it
    SELECT @SQLCmd AS [Command to execute];
    
    -- create the XML file on the file system
    EXECUTE master.sys.xp_cmdshell @SQLCmd;

    Simulation of your SQL, and it is working:

    DECLARE @tbl TABLE(field1 VARCHAR(30)); INSERT INTO @tbl VALUES ('some value'); DECLARE @XMLOutput XML; DECLARE @XMLOutputChar nvarchar(max) ;WITH XMLNAMESPACES('myNameSpace' as ns) SELECT @XMLOutput = ( SELECT field1 FROM @tbl FOR XML PATH('Testing'),TYPE, ROOT('TestingLoader'),ELEMENTS XSINIL ); SET @XMLOutputChar = '<?xml version="1.0" encoding="UTF-16"?>' + CONVERT(nvarchar(max),@XMLOutput); SELECT @XMLOutputChar AS XMLOutput;

    SELECT CAST(@XMLOutputChar AS XML);


    Output as NVARCHAR:

    XMLOutput
    <?xml version="1.0" encoding="UTF-16"?><TestingLoader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns="myNameSpace"><Testing><field1>some value</field1></Testing></TestingLoader>

    Output as XML:

    <TestingLoader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns="myNameSpace">
      <Testing>
        <field1>some value</field1>
      </Testing>
    </TestingLoader>
    Tuesday, July 30, 2019 2:19 PM
  •  Try removing the ‘encoding="UTF-8"’ part or specify "UTF-16".

    Tuesday, July 30, 2019 5:35 PM