0

SQL Server 2017 Express - I want to export xml data from the database to a file something.xml. I can query the data and write the data the into textfile. The only thing that's missing is the declaration.

Whatever I do, the declaration is removed instantly and the result is always the XML data only. This topic has been talked to death from what I've seen. But all the answers are several years old. Is there any chance to do export the XML data from T-SQL (no C#, no VB, no Javascript, no whatever) including the XML declaration line by now?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eric Nestrada
  • 39
  • 6
  • Show us your code, show us what you've tried. What is wrong with the results you have; explain why the results you get are wrong and show us the problem. You say you've seen this is "talked to death"; where? What answers have you tried to implement and why didn't they work? – Thom A Mar 01 '20 at 10:14
  • my code is all from the web: https://www.google.de/search?source=hp&ei=j6FbXqLdHM-LlwT5lIz4Aw&q=t-sql+export+xml+with+declaration&oq=t-sql+export+xml+with+declaration&gs_l=psy-ab.3..33i22i29i30.887.15749..16008...0.0..0.163.3529.14j19......0....1..gws-wiz.......0i131j0j0i22i30j0i8i13i30.P2wlC1dafPI&ved=0ahUKEwji4N3vmvnnAhXPxYUKHXkKAz8Q4dUDCAc&uact=5 I've tried nearly everything - but there is not one post or thread that is written recently. NO answer did work, it's always the same result. The xml data is written into the file, the declaration is not. I need both, declaration AND xml-data – Eric Nestrada Mar 01 '20 at 12:00

1 Answers1

0

The most important question is: Why do you need the declaration?

Important to know: This declaration is not a necessary part of the XML, but a hint for the consumer how to read the content.

I most cases this declaration tells the consumer about a text file's encoding. There are millions of XML files out there yelling encoding="utf8" or something like this, but - in fact - the file is utf16, plain ASCII or any other encoding. If the declaration is not telling the truth, it is better not to have such a declaration at all...

Within SQL-Server there are three options to store XML (not talking about binary or outdated text here): Best is native XML, which uses a nvarchar based hierarchy table under the hood, and (a bad choice, but) often seen is a string based column based on one of the two string types. You must know, that SQL-Server can deal with (var)char(x) (which is collation extended ASCII), and with n(var)char(x), which is UCS-2 (almost the same as utf16).

What would be the benefit to store XML with a declaration in such a scenario? SQL-Server can take the encoding directly from the type used for storage. On string level you can store literally anything, even invalid / not well-formed XML. But, at the moment you enter the native XML type, SQL-Server will omit any declaration and will refuse rule breaking XML.

To answer your question

The only chance to add the declaration, if you really need it, is to convert your XML to a string type and add the declaration on string level.

Keep in mind that

  • you cannot take this back into native XML without losing the declaration.
  • If you state any encoding you should make sure, that the written target file is not a liar.

Some years ago I had the same question (needed to add a stylesheet hint). You might read this related question (and a good answer by har07). My own answer there reflects the <?xml blahblah ?> specifically.

final hints

Starting with v2019 there is native utf-8 support with some special collations and starting with v2014SP2 there was UTF-8 support for BCP.

You can use this code to find a collation's encoding:

select [name] 
      ,[description]
      ,COLLATIONPROPERTY([name],'codepage') [codepage]
from sys.fn_helpcollations();

And - following this - you might use something like this

<?xml version="1.0" encoding="windows-1252"?>

To reflect the chosen encoding correctly.

General hint: Using the string-type NVARCHAR(MAX) together with utf-16 works in (almost) any case.

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114