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

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 XML
DECLARE @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.

Try opening the file in Notepad++ and view -> Show Symble -> All, and you can see if you have any characters — Brad
– Brad, Commented Jul 30, 2019 at 13:04
@Brad Using OP's code and a temp table of my own creation, I get the T of the TestingLoader root node name. <?xml version="1.0" encoding="UTF-8"?><TestingLoader... — digital.aaron
– digital.aaron, Commented Jul 30, 2019 at 15:06
@Michael can you show us how you have the OLE DB Source, Data Flow, and Flat File Destination configured? — digital.aaron
– digital.aaron, Commented Jul 30, 2019 at 15:08

Yitzhak Khabinsky · Accepted Answer · 2019-07-30 20:01:11Z

1

You re-posted this question on the Microsoft forum where I found it originally. I already answered it there. https://social.msdn.microsoft.com/Forums/en-US/f85ebd77-e7cb-4073-81e7-77a2ed5ab425/sql-server-ssis-export-as-xml-error-name-cannot-begin-with-the-character-hexadecimal-value?forum=sqlxml

answered Jul 30, 2019 at 20:01

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yahfoufi Over a year ago

Why not rewriting the answer summary, since it is not recommended to write a link only answer

LeppyR64 Over a year ago

I'm here today getting a 404 on that link wishing this weren't a link only answer :)

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related