how to insert XML Declaration

Question

how to insert XML Declaration

sam-433147

Ten Centuries

Points: 1221
More actions
January 27, 2017 at 1:25 am

#318334

for inserting the XML Declaration
i use the next code:
set @xml=(select xml_data from Xmltable)
set @xml_convert='<?xml version="1.0" encoding="UTF-8"?>'+cast(@xml as nvarchar(max));
IF OBJECT_ID('tempdb..##x') IS NOT NULL DROP TABLE ##x;
select @xml_convert AS x into ##x;
bcp "select * from table'" queryout C:\filename.dat -S @@servername -t -c -C 65001 -T
EXEC master..xp_cmdshell @sql
but the XML Declaration dose not appear in the xml file.
glad for any help

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

sam-433147 - Friday, January 27, 2017 1:25 AM
for inserting the XML Declaration
i use the next code:
set @xml=(select xml_data from Xmltable)
set @xml_convert='<?xml version="1.0" encoding="UTF-8"?>'+cast(@xml as nvarchar(max));
IF OBJECT_ID('tempdb..##x') IS NOT NULL DROP TABLE ##x;
select @xml_convert AS x into ##x;
bcp "select * from table'" queryout C:\filename.dat -S @@servername -t -c -C 65001 -T
EXEC master..xp_cmdshell @sql
but the XML Declaration dose not appear in the xml file.
glad for any help

You will have to add it in the bcp query, once in the XML type column, it is stripped off.
😎

Question, how are you making certain that the output is UTF-8? If it isn't, this whole exercise does not make sense.

sam-433147 Ten Centuries Points: 1221 More actions · Answer 2

Eirikur Eiriksson - Friday, January 27, 2017 1:30 AM
sam-433147 - Friday, January 27, 2017 1:25 AM
for inserting the XML Declaration
i use the next code:
set @xml=(select xml_data from Xmltable)
set @xml_convert='<?xml version="1.0" encoding="UTF-8"?>'+cast(@xml as nvarchar(max));
IF OBJECT_ID('tempdb..##x') IS NOT NULL DROP TABLE ##x;
select @xml_convert AS x into ##x;
bcp "select * from table'" queryout C:\filename.dat -S @@servername -t -c -C 65001 -T
EXEC master..xp_cmdshell @sql
but the XML Declaration dose not appear in the xml file.
glad for any help
You will have to add it in the bcp query, once in the XML type column, it is stripped off.
😎
Question, how are you making certain that the output is UTF-8? If it isn't, this whole exercise does not make sense.

thank's for the replay
-C 65001 in the bcp string make's it as utf-8
the file is UTF-8 encoding without a doubt
can you show me how to add it in the bcp query?

thanks ahead